Posted: 1 Mar 2017 13:39 EST Last activity: 6 Mar 2017 12:14 EST
Consume Oracle Stored Procedure that returns CLOB as OUT Parameter
Hello - Does PRPC support consuming a CLOB OUT parameter from a stored procedure? When we tried with this integration it works fine until the size of the CLOB is upto 32K bytes, when the CLOB data size/length exceeds 32K we are encountering "code: 6502 SQLState: 65000 Message: ORA-06502: PL/SQL: numeric or value error ".
Both the RequestJson and the ResponseJson are defined as Text properties in PRPC. I am thinking that PRPC considers these as VARCHAR types when doing to the SQL integration and tries to capture the stored procedure output in the ResponseJson element, since it cannot accommodate the CLOB if it is more than 32K it throws the SQL error.
Is there any other design approach in PRPC that can be considered to integrate with the stored procedures that return CLOB?
I see that the SR you have raised with Pega Support is assigned and the investigation has started. Please let us know for any updates. Meanwhile, I found this article on PDN which could be worth a look: RDB-SAVE gives exception when call a procedure
Hi Lochana - Thanks for your response. The issue in my case is different from what is indicated in the above article, in my scenario I am trying to retrieve CLOB data (RDB-List) using stored procedure and not saving the data (RDB-SAVE). Since there is no CLOB equivalent in Pega, I cannot specify as such any type as indicated in the article.
I did find another article on PDN, How to access CLOB Data but it talks about writing java code to meet the requirement on PRPC 6.2. I was wondering if there is a better way of implementing this integration in the newer versions of PRPC.
Sure, I will update this thread if I receive any updates from the support team or if I am able to resolve this by any other means.
We heard back from the Pega support on the SR associated with this issue. As per the support team, per the limitations mentioned in the PRPC 7.2.1 help file, SQL connectors cannot process columns of data type ARRAY, IMAGE, or CLOB (Character Large Object). The support team also indicated that starting from PRPC 7.2.2, the CLOB data type is supported. Since we are still on 7.2.1, we haven't tried if the CLOB support is provided by the Connect-SQL rule.
For now, we went ahead with a Java function as described in the article How to access CLOB Data. The only thing that we did differently than what is posted in the article is using the data source look up through JNDI name as configured on the application server rather than using the Data-Admin-DB-Name to get the connection details.
/* Look up the data source using the JNDI name to establish the connection to the database */
javax.naming.InitialContext ctx = new javax.naming.InitialContext();