Question
Cigna
US
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 ".
Sample Stored Procedure Definition:
\{CALL TEST_STORED_PROC( {MyServicePage.RequestJson IN},
{MyServicePage.ResponseJson OUT} )
\}
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?
Thank you!
Prashanth
Accepted Solution
Cigna
US
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();
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();
javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup(dbJNDIName);
Connection conn = ds.getConnection();
Hope this information helps others!
Thanks!
Prashanth
Pegasystems Inc.
IN
Hi Prashanth,
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
Please let us know if this is relevant/helpful.
Cigna
US
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.
Thanks!
Prashanth
Accepted Solution
Cigna
US
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();
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();
javax.sql.DataSource ds = (javax.sql.DataSource)ctx.lookup(dbJNDIName);
Connection conn = ds.getConnection();
Hope this information helps others!
Thanks!
Prashanth