Support Article

Error while calling SQL Function with RDB List



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.


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
l_encrypted VARCHAR2(15);
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.
Suggest Edit

Published June 30, 2016 - Updated October 8, 2020

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.