HCM Data Loader (HDL) is Oracle's primary tool for bulk-loading data into Fusion Cloud HCM. Whether you are migrating from a legacy system, loading new hires from a feed, or running mass updates, HDL is the mechanism that gets data into the application tables. This guide covers the .dat file format, every major business object, sample templates you can copy, the correct load order, and how to fix the errors you will inevitably hit.
On This Page
What Is HDL (and When to Use HSDL Instead)
HCM Data Loader (HDL) is a file-based bulk loading utility that processes .dat files containing pipe-delimited records. It writes directly to the HCM application tables through the same business object layer the UI uses, which means all validations, approvals, and business rules apply.
HDL vs. HSDL (HCM Spreadsheet Data Loader)
| Feature | HDL (.dat files) | HSDL (Spreadsheets) |
|---|---|---|
| Volume | Thousands to millions of records | Small batches (under 500 rows) |
| Automation | Fully scriptable, integrates with BIP/UCM | Manual upload via browser |
| Format | Pipe-delimited .dat text files | Excel/CSV spreadsheet templates |
| Use case | Migration, integrations, scheduled feeds | HR admin corrections, small ad-hoc loads |
| Error handling | Log files with line-level errors | Inline spreadsheet error messages |
| Business objects | All HCM business objects | Subset (most common ones) |
Rule of thumb: Use HDL for anything automated, repeatable, or over 100 records. Use HSDL when an HR admin needs to fix a handful of records manually and does not want to build a .dat file.
.dat File Format Explained
Every HDL file is a plain text file with a .dat extension. It contains three types of lines: METADATA, data rows, and optional comment lines.
METADATA Line
The first line of every component in a .dat file is the METADATA line. It declares which business object and component you are loading, and lists the columns in order.
METADATA|Worker|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonNumber|StartDate
The format is: METADATA|BusinessObject|Column1|Column2|Column3|...
- The first field is always the literal string
METADATA - The second field is the component name (e.g., Worker, WorkRelationship, PersonName)
- Remaining fields are the attribute names in the order your data rows will provide them
- You only include columns you are actually loading — omitted columns get default values
Operations: MERGE, DELETE, UPDATE
| Operation | Behavior | When to Use |
|---|---|---|
| MERGE | Insert if not found, update if exists (upsert) | New hires, updates, most loads |
| DELETE | Remove the record | Correcting bad data, removing duplicates |
| UPDATE | Update only — fails if record doesn't exist | When you want strict validation that the record exists |
The operation keyword goes in the data row (not the METADATA line). See the sample files below.
Key Format Rules
- Delimiter: Pipe character
|separates every field - Date format:
YYYY/MM/DD(e.g.,2026/01/15) - SourceSystemOwner: Identifies the source system (e.g.,
HRC_SQLLOADERfor manual loads, or your integration name) - SourceSystemId: Your unique key for the record in the source system. Must be unique per business object per SourceSystemOwner
- Encoding: UTF-8 without BOM
- Line endings: Unix (LF) preferred; Windows (CRLF) is tolerated
- Empty fields: Leave the space between pipes empty:
|value1||value3|
Common mistake: Using MM/DD/YYYY or DD-MON-YYYY date formats. HDL requires YYYY/MM/DD with forward slashes. Any other format causes silent data corruption or ORA errors.
Business Objects Reference
HDL organizes data into business objects, each containing one or more components. The Worker business object, for example, has sub-components for names, addresses, phones, and more. Here are the components you will use most often.
| Component | Business Object | Key Table | Description |
|---|---|---|---|
| Worker | Worker | PER_ALL_PEOPLE_F | Core person record. Creates the person and person number. |
| WorkRelationship | Worker | PER_PERIODS_OF_SERVICE | Employment relationship (EMP, CWK). Defines period of service. |
| WorkTerms | Worker | PER_ALL_ASSIGNMENTS_F | Work terms (primary assignment terms). Links to legal employer. |
| Assignment | Worker | PER_ALL_ASSIGNMENTS_F | Assignment details: job, position, department, location, grade, manager. |
| PersonName | Worker | PER_PERSON_NAMES_F | Person names (GLOBAL, LOCAL types). First, last, middle, title. |
| PersonAddress | Worker | PER_ADDRESSES_F | Home, mailing, and other address types. |
| PersonPhone | Worker | PER_PHONES | Phone numbers (work, home, mobile, fax). |
| PersonEmail | Worker | PER_EMAIL_ADDRESSES | Email addresses (work, personal). |
| PersonLegislativeData | Worker | PER_PERSON_LEGISLATIVE_F | Legislation-specific data: marital status, gender, ethnicity. |
| PersonNationalIdentifier | Worker | PER_NATIONAL_IDENTIFIERS | National IDs (SSN, SIN, NI Number) by legislation. |
Tip: All of these components belong to the same Worker business object and can be included in a single .dat file. HDL processes them in the correct dependency order automatically within the same file.
Sample .dat Files (Copy-Paste Templates)
Below are complete, working .dat file samples for the most common scenarios. Copy them, replace the sample values, and upload.
1. New Hire (Full)
Creates a new employee with name, work relationship, work terms, and assignment. This is the minimum viable new hire file.
METADATA|Worker|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonNumber|StartDate MERGE|HRC_SQLLOADER|WRKR-10001|2026/03/01|4712/12/31||2026/03/01 METADATA|PersonName|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|LegislationCode|NameType|FirstName|MiddleNames|LastName|Title MERGE|HRC_SQLLOADER|NAME-10001|WRKR-10001|2026/03/01|4712/12/31|US|GLOBAL|Jane|M|Doe|Ms. METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|DateStart|WorkerType|LegalEmployerName|ActionCode MERGE|HRC_SQLLOADER|WR-10001|WRKR-10001|2026/03/01|E|US Legal Entity|HIRE METADATA|WorkTerms|SourceSystemOwner|SourceSystemId|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|AssignmentType|AssignmentName|AssignmentStatusTypeCode|BusinessUnitShortCode|ActionCode MERGE|HRC_SQLLOADER|WT-10001|WR-10001|WRKR-10001|2026/03/01|4712/12/31|1|Y|ET|ET10001|ACTIVE_PROCESS|US Business Unit|HIRE METADATA|Assignment|SourceSystemOwner|SourceSystemId|WorkTermsAssignmentId(SourceSystemId)|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|AssignmentType|AssignmentName|AssignmentStatusTypeCode|BusinessUnitShortCode|JobCode|DepartmentName|LocationCode|ActionCode MERGE|HRC_SQLLOADER|ASG-10001|WT-10001|WR-10001|WRKR-10001|2026/03/01|4712/12/31|1|Y|E|E10001|ACTIVE_PROCESS|US Business Unit|SOFTWARE_ENG|Engineering|US-HQ|HIRE
2. Termination
Terminates an existing employee by updating the WorkRelationship with a termination action and date.
METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|DateStart|ActualTerminationDate|ActionCode|ActionReasonCode|NotifiedTerminationDate|LastWorkingDate
MERGE|HRC_SQLLOADER|WR-10001|WRKR-10001|2026/03/01|2026/06/15|TERMINATION|RESIGNATION|2026/06/01|2026/06/15
3. Transfer (Department/Location Change)
Changes an employee's department and location effective on a future date. Uses the existing assignment SourceSystemId.
METADATA|Assignment|SourceSystemOwner|SourceSystemId|WorkTermsAssignmentId(SourceSystemId)|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|AssignmentType|AssignmentStatusTypeCode|DepartmentName|LocationCode|ActionCode|ActionReasonCode
MERGE|HRC_SQLLOADER|ASG-10001|WT-10001|WR-10001|WRKR-10001|2026/07/01|4712/12/31|1|Y|E|ACTIVE_PROCESS|Marketing|US-WEST|TRANSFER|BUSINESS_NEED
4. Rehire
Rehires a previously terminated employee. Creates a new WorkRelationship with a REHIRE action, plus new WorkTerms and Assignment.
METADATA|WorkRelationship|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|DateStart|WorkerType|LegalEmployerName|ActionCode MERGE|HRC_SQLLOADER|WR-10001-R|WRKR-10001|2026/09/01|E|US Legal Entity|REHIRE METADATA|WorkTerms|SourceSystemOwner|SourceSystemId|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|AssignmentType|AssignmentName|AssignmentStatusTypeCode|BusinessUnitShortCode|ActionCode MERGE|HRC_SQLLOADER|WT-10001-R|WR-10001-R|WRKR-10001|2026/09/01|4712/12/31|1|Y|ET|ET10001-R|ACTIVE_PROCESS|US Business Unit|REHIRE METADATA|Assignment|SourceSystemOwner|SourceSystemId|WorkTermsAssignmentId(SourceSystemId)|PeriodOfServiceId(SourceSystemId)|PersonId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|AssignmentType|AssignmentName|AssignmentStatusTypeCode|BusinessUnitShortCode|JobCode|DepartmentName|LocationCode|ActionCode MERGE|HRC_SQLLOADER|ASG-10001-R|WT-10001-R|WR-10001-R|WRKR-10001|2026/09/01|4712/12/31|1|Y|E|E10001-R|ACTIVE_PROCESS|US Business Unit|SR_SOFTWARE_ENG|Engineering|US-HQ|REHIRE
5. Mass Update (Email & Phone)
Updates email addresses and phone numbers for multiple employees in a single file.
METADATA|PersonEmail|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|DateFrom|DateTo|EmailType|EmailAddress MERGE|HRC_SQLLOADER|EMAIL-10001|WRKR-10001|2026/01/01|4712/12/31|W1|[email protected] MERGE|HRC_SQLLOADER|EMAIL-10002|WRKR-10002|2026/01/01|4712/12/31|W1|[email protected] MERGE|HRC_SQLLOADER|EMAIL-10003|WRKR-10003|2026/01/01|4712/12/31|W1|[email protected] METADATA|PersonPhone|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|DateFrom|DateTo|PhoneType|CountryCodeNumber|AreaCode|PhoneNumber MERGE|HRC_SQLLOADER|PH-10001|WRKR-10001|2026/01/01|4712/12/31|W1|1|555|0100001 MERGE|HRC_SQLLOADER|PH-10002|WRKR-10002|2026/01/01|4712/12/31|W1|1|555|0100002 MERGE|HRC_SQLLOADER|PH-10003|WRKR-10003|2026/01/01|4712/12/31|W1|1|555|0100003
6. Person Name Change
Updates a person's last name (e.g., after marriage). Creates a new date-effective row on PER_PERSON_NAMES_F.
METADATA|PersonName|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|LegislationCode|NameType|FirstName|LastName
MERGE|HRC_SQLLOADER|NAME-10001|WRKR-10001|2026/04/15|4712/12/31|US|GLOBAL|Jane|Smith
Load Order Diagram
When loading multiple business objects in separate files (not combined in a single Worker .dat), you must follow this dependency order. Objects on the right depend on objects to their left.
Pro tip: If you include all components in a single .dat file under the Worker business object, HDL handles the dependency order automatically. Separate files are only needed when you split loads into multiple uploads.
Error Troubleshooting
These are the most common HDL errors. Check the HDLActivityStream.log and HDImportProcess.log files in your import results for details.
| Error | Cause | Fix |
|---|---|---|
ORA-01861: literal does not match format string |
Wrong date format in the .dat file | Use YYYY/MM/DD with forward slashes. No dashes, no DD-MON-YYYY. |
SourceSystemId already exists |
Duplicate SourceSystemId for the same SourceSystemOwner + business object | Make SourceSystemIds unique. Append sequence numbers or use GUIDs. |
Invalid attribute value for LegalEmployerName |
The legal employer name does not exactly match what is configured | Check HR_ALL_ORGANIZATION_UNITS for the exact name string. Case-sensitive. |
Referenced object not found: PersonId |
The Worker record was not loaded before dependent components | Ensure Worker is loaded first, or include all components in one file. |
No matching row found for ActionCode |
Invalid action code for the context (e.g., using HIRE on a rehire) | Check lookup codes for valid ACTION_TYPE values. Use REHIRE for rehires. |
Column count mismatch |
Number of pipe-delimited values does not match METADATA column count | Count the pipes in your METADATA line and data lines. They must match exactly. |
Attribute BusinessUnitShortCode is invalid |
Business unit short code does not match any configured BU | Query HR_ALL_ORGANIZATION_UNITS with classification BUSINESS_UNIT. |
Effective date range overlap |
A new date-effective row overlaps with an existing one | Check EffectiveStartDate and EffectiveEndDate. End dates should be 4712/12/31 for current rows. |
UTF-8 BOM detected |
File was saved with UTF-8 BOM encoding | Save as UTF-8 without BOM. In Notepad++: Encoding → UTF-8. |
Worker already has an active work relationship |
Trying to HIRE someone who already has an active employment | Use REHIRE action if the person was previously terminated, or check if they already have an active record. |
Invalid lookup code for WORKER_TYPE |
WorkerType value is not E, C, or N | Use E (Employee), C (Contingent Worker), or N (Nonworker). See lookup codes reference. |
File encoding error / Garbage characters |
File was saved in a non-UTF-8 encoding (e.g., Windows-1252) | Convert to UTF-8. Remove any special characters outside basic ASCII + Unicode. |
Best Practices
Batch Size
- Keep files under 10,000 records per component for reliable processing
- For migration, split into batches of 5,000 and load sequentially
- Very large loads (>50,000) should run overnight and use the async submission mode
Testing
- Always test in a sandbox or test environment first
- Start with 5-10 records to validate the format before loading thousands
- Use the VALIDATE import mode (available in some releases) to dry-run without committing
- Review the log files after every load, even if the status shows success
Date Formats
- Always
YYYY/MM/DDwith forward slashes 4712/12/31is the "end of time" date for open-ended effective records- Do not use timestamps — HDL only accepts dates
File Encoding
- UTF-8 without BOM
- Unix line endings (LF) preferred
- No trailing spaces after the last pipe on each line
- No blank lines between records (blank lines between components are OK)
SourceSystemOwner & SourceSystemId
- Use a consistent SourceSystemOwner for your integration (e.g.,
HRC_SQLLOADER,LEGACY_HR) - SourceSystemId must be unique per component + SourceSystemOwner
- Use predictable IDs (e.g.,
WRKR-{EmpID},ASG-{EmpID}) so you can reference them across components - Cross-references use the format
PersonId(SourceSystemId)to link to the Worker
Finding HDL in the UI
To access HCM Data Loader in Oracle Fusion Cloud:
- Navigate to My Client Groups → Data Exchange
- Select HCM Data Loader from the tasks panel
- Click Import to upload your .dat file (zipped in a .zip archive)
- Monitor progress under Import and Load Data
- Download results and log files from the View Import Status page
File packaging: HDL expects a .zip file containing one or more .dat files. The .dat files can be named anything, but must have the .dat extension. Multiple .dat files in one zip are processed in alphabetical order.
For automated/scheduled loads, you can upload files to UCM (WebCenter Content) and trigger HDL via a BIP report or REST API call.
Look Up Table Schemas for HDL Attributes
Every HDL attribute maps to a column in the underlying HCM table. Search any table to see exact column names, data types, and lengths.
Search Tables