Support Article

Not able to save Property of type CLOB for values > 4k char

SA-38839

Summary



Unable to save property which is mapped to CLOB data type in Database, if value has more than 4000 characters.


Error Messages



Encountered problem when performing a Commit
com.pega.pegarules.pub.database.DatabaseException: ORA-01461: can bind a LONG value only for insert into a LONG column

From: (unknown)
SQL: MERGE INTO PEGADATA.TT_COLLECT_QUEUE_NOTIFICATIONSt USING (SELECT ? "pzInsKey" , CURRENT_TIMESTAMP"pxCommitDateTime" , ? "AccountNumber" , ? "CustomerID" , ? "ERRORCODE" , ? "ERRORDESCRIPTION" , ? "InboundMessage" , ? "NotificationType" , ? "ORDERBYITEM1" , ? "ORDERBYITEM2" , ? "pxCreateDateTime" , ? "pxInsName" , ? "pxLastExecutionDateTime" , ? "pxObjClass" , ? "pxProcessingNodeId" , ? "pxSaveDateTime" , ? "pyAgentName" , ? "pyAttempts" , ? "pyItemId" , ? "pyItemStatus" , ? "pyMaxAttempts" , ? "pyMinimumDateTimeForProcessing" , ? "pyPriority" , ? "pySelectId" , ? "pzPVStream" FROM dual) inputs ON (t.pzInsKey = inputs."pzInsKey") WHEN MATCHED THEN UPDATE SET ACCOUNTID = inputs."AccountNumber" , CUSTOMERID = inputs."CustomerID" , ERRORCODE = inputs."ERRORCODE" , ERRORDESCRIPTION = inputs."ERRORDESCRIPTION" , INBOUNDMESSAGE = inputs."InboundMessage" , NOTIFICATIONTYPE = inputs."NotificationType" , ORDERBYITEM1 = inputs."ORDERBYITEM1" , ORDERBYITEM2 = inputs."ORDERBYITEM2" , PXCREATEDATETIME = inputs."pxCreateDateTime" , PXINSNAME = inputs."pxInsName" , PXLASTEXECUTIONDATETIME = inputs."pxLastExecutionDateTime" , PXOBJCLASS = inputs."pxObjClass" , PXPROCESSINGNODEID = inputs."pxProcessingNodeId" , PXSAVEDATETIME = inputs."pxSaveDateTime" , PYAGENTNAME = inputs."pyAgentName" , PYATTEMPTS = inputs."pyAttempts" , PYITEMID = inputs."pyItemId" , PYITEMSTATUS = inputs."pyItemStatus" , PYMAXATTEMPTS = inputs."pyMaxAttempts" , PYMINIMUMDATETIMEFORPROCESSING = inputs."pyMinimumDateTimeForProcessing" , PYPRIORITY = inputs."pyPriority" , PYSELECTID = inputs."pySelectId" , pzPVStream = inputs."pzPVStream" , pxCommitDateTime = CURRENT_TIMESTAMP WHEN NOT MATCHED THEN INSERT (pzInsKey , pxCommitDateTime , ACCOUNTID , CUSTOMERID , ERRORCODE , ERRORDESCRIPTION , INBOUNDMESSAGE , NOTIFICATIONTYPE , ORDERBYITEM1 , ORDERBYITEM2 , PXCREATEDATETIME , PXINSNAME , PXLASTEXECUTIONDATETIME , PXOBJCLASS , PXPROCESSINGNODEID , PXSAVEDATETIME , PYAGENTNAME , PYATTEMPTS , PYITEMID , PYITEMSTATUS , PYMAXATTEMPTS , PYMINIMUMDATETIMEFORPROCESSING , PYPRIORITY , PYSELECTID , pzPVStream) VALUES (inputs."pzInsKey" , CURRENT_TIMESTAMP , inputs."AccountNumber" , inputs."CustomerID" , inputs."ERRORCODE" , inputs."ERRORDESCRIPTION" , inputs."InboundMessage" , inputs."NotificationType" , inputs."ORDERBYITEM1" , inputs."ORDERBYITEM2" , inputs."pxCreateDateTime" , inputs."pxInsName" , inputs."pxLastExecutionDateTime" , inputs."pxObjClass" , inputs."pxProcessingNodeId" , inputs."pxSaveDateTime" , inputs."pyAgentName" , inputs."pyAttempts" , inputs."pyItemId" , inputs."pyItemStatus" , inputs."pyMaxAttempts" , inputs."pyMinimumDateTimeForProcessing" , inputs."pyPriority" , inputs."pySelectId" , inputs."pzPVStream")

Caused by SQL Problems.
Problem #1, SQLState 72000, Error code 1461: java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column

Problem #2, SQLState 72000, Error code 1461: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

<...>

Caused by:
java.sql.BatchUpdateException: ORA-01461: can bind a LONG value only for insert into a LONG column
at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:12296)
at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:246)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteBatch(WSJdbcPreparedStatement.java:1037)
at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeBatch(WSJdbcStatement.java:843)
at com.pega.pegarules.data.internal.access.DatabasePreparedStatementImpl.executeBatch(DatabasePreparedStatementImpl.java:534)
at com.pega.pegarules.data.internal.access.ConnectionStatementStore.executeBatchForAllStatements(ConnectionStatementStore.java:198)
... 75 more


Steps to Reproduce

  1. Create a text property which is of CLOB data type in Data base.
  2. Set value which has more than 4000 characters.
  3. Save and commit changes.


Root Cause



Known limitation.

Resolution



Apply HFix-33984.

Published June 1, 2017 - Updated July 21, 2017

Have a question? Get answers now.

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