SA-4976

Summary

How to call a custom SQL function from Pega?

Error Messages

There was a problem performing a database query: There was a problem getting a list: code: 6550 SQLState: 65000 Message: ORA-06550: line 1, column 13: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored \{{resultset} = call PEGADATA.encrypt_data_1({DataPage.CardNumber},{DataPage.VKey}) \} 0 {? = call PEGADATA.encrypt_data_1(?,?) }

Steps to Reproduce

1. Create an SQL function in the database, which takes two VARCHAR2 parameters.
2. Create an RDB List that calls the stored procedure using the following statement:
\{{resultset} = call PEGADATA.encrypt_data_1({DataPage.CardNumber},{DataPage.VKey}) \}

Root Cause

{resultset} keyword is not supported by the database.

Resolution

The explanation for this behavior is as follows:
Calling stored procedure that returns result is not supported. This is described in the Help topic.

Follow the steps given below to call a simple SQL function (not stored procedure) from Pega 7.

1. Create the SQL Function.
Here is a simplified function to output "test" with VARCHAR2 return type.
create or replace Function encrypt_data_out( p_data IN VARCHAR2, V_KEY IN VARCHAR2) Return VARCHAR2
IS
l_encrypted VARCHAR2(15);
BEGIN
l_encrypted := 'test';
Return l_encrypted;
END encrypt_data_out;
/

2. Create the RDB List.
Enter the following SQL in the Browse tab:
select encrypt_data_out('a', 'b') as "MyOutput" from dual

Call the RDB list using an activity or other rule to test it. The example here shows an activity calling the RDB List.



The returned value will be stored in a pxResults Page List.