Oracle Transactional Business Intelligence

Products Banner

Extracting Goal SuccessCriteriaText from hrc_txn_data that is greater than 4000 Characters using Ora



Extracting SuccessCriteriaText for Performance Goals in Draft or Pending Approval from hrc_txn_data using SQL when SuccessCriteriaText is having more than 4000 Characters


After the Classic to Responsive Conversion, Oracle is using the  hrc_txn_data records for capturing the Goals that are in Draft of Pending Approval (prior to RUX all these details are captured in the HRG GOALS record), data_cache field within the record hrc_txn_data is holding the details of the Employee Goals in an XML formatted output. We have a requirement to extract the Details of the Goals prior to the approval for BI reporting Purpose. When we are trying to use the Oracle provided XMLDB functions like ExtractValue and/or XMLTABLE the SQL is erroring out when we have more than 4000 characters which is always the case.

Please note from UI standpoint Oracle is allowing the user to key in a maximum of 4GB

We are looking for a SQL to fetch the value from a node within an XML file stored in a database table column (CLOB) using the Oracle XMLDB functions with one of the node having a value more than 4000 characters

  • Oracle is capturing the transaction data in an XML format within a CLOB field (field data_cache in the hrc_txn_data table).
  • One of the node within the XML (stored in the database field within a table) is holding data more than 4000 characters encapsulated with a CDATA
  • We tried using the Oracle recommended XMLDB function extractvalue to fetch the values by parsing the XML data and able to get the data for all the nodes as expected except for one of the node within the XML stored in a database table that has more than 4000 characters
  • The SQL is erroring out with an error ORA-01706: user function result value was too large

We are looking for assistance in coming up with the SQL query so that we can fetch the value captured within the XML node that has more than 4000 characters given the challenges we are facing with the extractvalue Oracle XMLDB function

Please do refer the attached sample XML content captured in the data_cache field that we are trying to parse and extract the data from SuccessCriteriaText, appreciate if any one has encountered the similar issue and/or requirement and let us know what has been done to accomplish this.




Oracle Cloud HCM 20C

Code Snippet

Please refer the attached files that explain in deatil on the SQL code we are using in both the options using the extract Value function that is failing with an error ORA-01706: user function result value was too large and the other option we tried using the XML Table (refer the Option2_XMLTABLE.sql) but the  SuccessCriteriaText is retruning blank value.