Thursday, 9 July 2026

Oracle Cloud HCM: Recovering HDL Files and Troubleshooting Import Errors Using SQL

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.

  1. Zip File
  2. Data File
  3. Import Validation
  4. Metadata
  5. Hierarchy
  6. Logical Object
  7. Physical Row
  8. 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.

at
Labels: , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)