Oracle Cloud HCM: Recovering HDL Files and Troubleshooting Import Errors Using SQL
Introduction
One of the biggest challenges during Oracle Cloud HCM support is troubleshooting an HDL load after it has already been processed.
A common scenario looks like this:
- An HDL was loaded several weeks or months ago.
- The original .dat file is no longer available.
- The consultant who loaded it has left the project.
- A business user reports incorrect data.
- You need to determine exactly what was loaded before preparing a correction HDL.
Since HDL imports are processed through Oracle's internal Import and Load Data framework, many consultants assume there is no way to retrieve the original file contents.
Fortunately, Oracle stores the imported HDL data in several HCM Data Loader tables. With a few SQL queries, you can reconstruct the original HDL file, review each physical line, and analyze errors generated during processing.
In this article, we will look at two practical SQL techniques:
- Reconstructing previously loaded HDL files
- Viewing HDL errors at every processing stage
Business Scenario
Imagine receiving the following request from Payroll:
An employee's salary was loaded incorrectly three months ago. We don't have the original HDL file anymore, but we need to understand what was imported before creating a correction.
Without the original HDL file, most teams begin recreating the data manually.
Instead, Oracle already stores every imported HDL line inside the database. With the appropriate SQL query, you can rebuild the original .dat file almost exactly as it was loaded.
Recovering Previously Loaded HDL Files
Oracle stores every HDL file line in the HRC_DL_FILE_LINES table.
Using the Data Set and Business Object tables, we can reconstruct the HDL file.
The following SQL returns every line in the HDL file in the original sequence.
SELECT
hdds.ucm_content_id,
hdds.data_set_name,
hddsbo.data_file_name,
hdfl.seq_num AS line_sequence,
hdfl.text AS hdl_line_text
FROM hrc_dl_data_sets hdds,
hrc_dl_data_set_bus_objs hddsbo,
hrc_dl_file_lines hdfl
WHERE 1 = 1
AND hdds.data_set_id = hddsbo.data_set_id
AND hddsbo.data_set_bus_obj_id = hdfl.data_set_bus_obj_id
-- AND hdds.ucm_content_id = 'UCMFA06411541'
AND hdds.data_set_name = 'Salary_6278_06302026.zip'
ORDER BY
hddsbo.data_file_name,
hdfl.seq_num;
Searching by UCM Content ID
If you know the uploaded UCM document ID instead of the zip file name, modify the WHERE clause.
WHERE hdds.ucm_content_id = 'UCMFA06411541'
This is particularly useful when reviewing historical HDL loads from production.
Typical Output
| UCM Content ID |
Data File |
Sequence |
HDL Line |
| UCMFA06411541 |
Salary.dat |
1 |
METADATA... |
| UCMFA06411541 |
Salary.dat |
2 |
MERGE... |
| UCMFA06411541 |
Salary.dat |
3 |
MERGE... |
Because the results are returned in sequence order, they can easily be copied into a new .dat file when preparing correction HDL.
Why This Query Is Useful
This query is useful in many implementation and support situations, such as:
- Original HDL file lost
- Reviewing historical data loads
- Building correction HDL files
- Comparing production versus test loads
- Investigating unexpected data changes
- Performing audit activities
Instead of recreating HDL manually, you can extract what Oracle already imported.
HDL Error Processing
Recovering the HDL file is only half of the story.
The next challenge is understanding where the import failed.
Many consultants only review the Import and Load Data UI. However, Oracle stores detailed messages throughout every stage of HDL processing.
Understanding these stages makes troubleshooting significantly easier.
HDL Processing Stages
Oracle validates HDL through several layers before data reaches the application.
- Zip File
- Data File
- Import Validation
- Metadata
- Hierarchy
- Logical Object
- Physical Row
- Service Processing
Each stage validates a different portion of the HDL. Knowing where an error occurs often tells you exactly what needs to be corrected.
Understanding Each Error Type
1. Zip File Errors
These occur before Oracle even reads the HDL.
- Invalid ZIP archive
- Corrupted upload
- Unsupported file format
2. Data File Errors
Oracle validates the contents of each .dat file.
- Missing data file
- Invalid encoding
- File structure issues
3. Import Validation Errors
These occur while Oracle imports individual HDL lines.
- Invalid attribute values
- Invalid dates
- Missing required fields
4. Metadata Errors
Oracle validates every METADATA line.
- Misspelled attribute names
- Incorrect business object
- Unsupported HDL version
5. Hierarchy Errors
Oracle validates parent-child relationships.
- Child record loaded before parent
- Invalid hierarchy sequence
- Missing parent object
6. Logical Object Errors
Logical validation occurs after hierarchy validation.
- Duplicate logical objects
- Missing unique keys
- Invalid HDL object relationships
7. Physical Row Errors
These errors occur at the individual MERGE line level.
- Duplicate source keys
- Invalid effective dates
- Business rule violations
8. Service Errors
These are generated during the final application service call.
- Object validation failures
- Payroll business rule errors
- Security violations
- Unexpected application exceptions
A Single Query for Complete Error Analysis
Rather than searching multiple tables individually, you can use a consolidated SQL query that retrieves HDL errors from every processing stage.
The query categorizes messages into:
- Zip File
- Data File
- Import
- Metadata
- Hierarchy
- Logical Object
- Physical Row
- Service Error
It also returns useful details such as:
- Message Type
- Error Message
- Stack Trace
- File Name
- Line Number
- Metadata Line
- HDL File Line
- Request ID
- UCM Content ID
Having all error information in one report makes it much easier to determine exactly where the HDL processing failed.
SELECT 1 orderby
, 'Zip File' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, to_number(null) seq_num
, '' ui_user_key
, '' metadata
, '' file_line
, ds.request_id
, ds.data_set_name
, '' data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_sets ds
WHERE l.message_source_table_name = 'HRC_DL_DATA_SETS'
AND l.message_source_line_id = ds.data_set_id
UNION
SELECT 2 orderby
, 'Data File' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, to_number('') seq_num
, '' ui_user_key
, '' metadata
, '' file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
WHERE l.message_source_table_name = 'HRC_DL_DATA_SET_BUS_OBJS'
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
UNION
SELECT 3 orderby
, 'Import' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, '' ui_user_key
, '' metadata
, fl.text file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = 'HRC_DL_FILE_LINES'
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND fl.line_id = l.message_source_line_id
UNION
SELECT 4 orderby
, 'METADATA' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, '' ui_user_key
, fl.text metadata
, '' file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_file_headers fh
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = 'HRC_DL_FILE_HEADERS'
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND fh.header_id = l.message_source_line_id
AND fl.line_id = fh.line_id
UNION
SELECT 5 orderby
, 'Hierarchy' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, '' ui_user_key
, (SELECT hl.text
FROM hrc_dl_file_lines hl
, hrc_dl_file_headers fh
WHERE fh.header_id = fr.header_id
AND hl.line_id = fh.line_id) metadata
, fl.text file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_file_rows fr
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = 'HRC_DL_FILE_ROWS'
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND fr.row_id = l.message_source_line_id
AND fl.line_id = fr.line_id
UNION
SELECT 6 orderby
, 'Logical Object' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, ll.ui_user_key ui_user_key
, (SELECT hl.text
FROM hrc_dl_file_lines hl
, hrc_dl_file_headers fh
WHERE fh.header_id = fr.header_id
AND hl.line_id = fh.line_id) metadata
, fl.text file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_logical_lines ll
, fusion.hrc_dl_file_rows fr
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = 'HRC_DL_LOGICAL_LINES'
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND ll.logical_line_id = l.message_source_line_id
AND fr.logical_line_id = ll.logical_line_id
AND fl.line_id = fr.line_id
UNION
SELECT 7 orderby
, 'Physical Row' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, fl.seq_num
, pl.ui_user_key || ' ' || pl.ui_date_from || ' ' || pl.ui_date_to ui_user_key
, (SELECT hl.text
FROM hrc_dl_file_lines hl
, hrc_dl_file_headers fh
WHERE fh.header_id = fr.header_id
AND hl.line_id = fh.line_id) metadata
, fl.text file_line
, ds.request_id
, ds.data_set_name
, bo.data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_data_set_bus_objs bo
, fusion.hrc_dl_data_sets ds
, fusion.hrc_dl_physical_lines pl
, fusion.hrc_dl_file_rows fr
, fusion.hrc_dl_file_lines fl
WHERE l.message_source_table_name = 'HRC_DL_PHYSICAL_LINES'
AND bo.data_set_bus_obj_id = l.data_set_bus_obj_id
AND ds.data_set_id = bo.data_set_id
AND pl.physical_line_id = l.message_source_line_id
AND fr.row_id = pl.row_id
AND fl.line_id = fr.line_id
UNION
SELECT DISTINCT 8 orderby
, 'Service Error' err_location
, l.message_type
, l.msg_text
, l.stack_trace
, to_number(null)
, ''
, '' metadata
, '' file_line
, ds.request_id
, ''
, '' data_file_name
, ds.ucm_content_id
FROM fusion.hrc_dl_message_lines l
, fusion.hrc_dl_service_requests sr
, fusion.hrc_dl_data_sets ds
WHERE l.message_source_table_name = 'HRC_DL_SERVICE_REQUESTS'
AND l.message_source_line_id = sr.service_call_id;
Common Production Support Scenarios
These SQL queries are particularly useful for:
- Recovering lost HDL files
- Investigating failed HDL loads
- Payroll support
- Production issue analysis
- Data conversion validation
- Building correction HDL files
- Audit requests
- Comparing environments
Best Practices
When supporting HDL imports, consider the following recommendations:
- Keep the original HDL zip file whenever possible.
- Record the HDL Data Set Name and UCM Content ID for every production deployment.
- Use UCM Content ID as the primary reference when troubleshooting historical loads.
- Review the earliest error in the processing hierarchy before investigating downstream errors.
- Validate correction HDL files in a lower environment before deploying to production.
Final Thoughts
Oracle HCM Data Loader stores significantly more information than many consultants realize. By querying the HDL repository tables, implementation teams can recover historical HDL files, reconstruct lost .dat files, and troubleshoot errors at every stage of the load process.
These SQL techniques are very useful during production support, especially when the original HDL files are unavailable or when historical data changes need to be investigated.
Rather than relying only on the Import and Load Data user interface, leveraging the underlying HDL tables provides deeper insight into both the data that was loaded and the exact point where processing failed.
Important Tip
One of the first things I recommend after every production HDL deployment is recording the Data Set Name, UCM Content ID, and Request ID.
Even if the original ZIP file is lost months later, these identifiers allow you to reconstruct the HDL contents and investigate errors directly from the HCM Data Loader repository tables, making future support significantly easier.
