Support Article
ORA-24345 on querying column length greater than 4000 characters
SA-46808
Summary
When running a report definition, ORA-24345 error occurs for certain instances whose property value length is greater than 4000
Note: HFix-35518 and HFix-35527, in Pega 7.1.7, provided limited CLOB support. Compatible support must be provided in Pega 7.2.
Error Messages
** An error occurred on executing the query for the report definition - There was a problem getting a list: code: 24345 SQLState: 99999 Message: ORA-24345: A Truncation or null fetch error occurred.
Steps to Reproduce
- Create a text area with a text property which is greater than 4000 characters.
- Create a report which includes the property.
- Run the report (including these columns).
Root Cause
This issue was determined to be a product enhancement request. A database is limited to 4000 bytes in the varchar2 column. For a property value that is greater than 4000, a CLOB column must be used; however, support for a CLOB was not available until Pega 7.2.2.
Resolution
Support for CLOB in Obj-Save and to retrieve the entire CLOB content in a report definition is backported to 7.2. When retrieving the entire CLOB content, the report operations on that column (such as, sort, grouping, joins, and so on) are not available.
- Apply HFix-38016 and HFix-38532.
- Create a dynamic system setting:
'reporting/retrieveFullClobContent on 'Pega-RULES' and set the value to 'true'. - For the property value that is greater than 4000 characters, alter the table to add the column as a CLOB.
- Restart the system.
- Upon restart, navigate into the system and open the Data-Admin-DB-Table rule. Resave the rule and run the 'test connection' (a warning about the CLOB column appears).
- Run the column populator tool to populate the newly exposed column in any existing instances.
Published November 22, 2017 - 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.