Categories
- All Categories
- 93 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14.2K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 53 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations Gallery
- 2 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Extracting Goal SuccessCriteriaText from hrc_txn_data that is greater than 4000 Characters using Ora
Summary
Extracting SuccessCriteriaText for Performance Goals in Draft or Pending Approval from hrc_txn_data using SQL when SuccessCriteriaText is having more than 4000 Characters
Content
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.
Version
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.
Comments
-
Hi,
Is this issue resolved? we are also facing same issue, please help with the work around?
0 -
Hello @Ravindra Paidiparthi,
I understand you areasking a question regarding extracting data from HRC_TXN_DATA table.
I think your concern is addressed here, please review this document :
If my response has answered your question or assisted you with yourconcern, please click"yes" below to accept the answer or comment with any additionalqueries. You can also read the Cloud Customer Connect Guidelines for Accepted Answer
Regards,
Gaurav0