SQLLDR in Oracle: Advanced SQL Loader Guide

SQL Loader(SQL*Loader) is a very powerful command line utility offered by Oracle to load data from external(csv, excel, dat) files into Oracle Database tables. It offers various functionalities, including different loading modes, character sets, and error handling mechanisms. Sqlldr is highly efficient for importing large datasets, supporting various file formats and loading methods. This guide provides a structured overview of SQL Loader in Oracle Apps R12 for EBS developers.

Components of SQL*Loader

1. Control File: Defines how data should be loaded.
2. Data File: Contains the actual data to be loader into Database Table.
3. Log File: SQLLDR wrote details of the load process.
4. Bad File: Sqlldr stores records with errors.
5. Discard File: Sqlldr stores records that do not meet specified conditions.

SQL Loader in Oracle Syntax

You need to prepare a control file based on the received datafile. Then, ftp the control file and data file in the server directory and execute the below command using Putty.

sqlldr apps/p_apps_pwd control=controlfile.ctl log=logfile.log

Control File for SQL Loader in Oracle

A control file for SQL Loader typically consists of:

• LOAD DATA: Indicates the beginning of a data loading operation
• INFILE: Defines the input data file.
• INTO TABLE: Defines the target table.
• FIELDS TERMINATED BY: Specifies the field delimiter.
• TRAILING NULLCOLS: Allows missing values to be treated as NULL.

Example of Control File for SQL Loader in Oracle

LOAD DATA
INFILE 'employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(emp_id, emp_name, emp_salary, hire_date DATE 'YYYY-MM-DD')

SQLLDR Loading Modes

1. INSERT: Loads data only if the table is empty.
2. APPEND: Adds data to an existing table.
3. REPLACE: Deletes existing records before inserting new ones.
4. TRUNCATE: Uses the TRUNCATE command before loading data.

SQLLDR Loading Paths

1. Conventional Path: Uses standard SQL INSERT statements.
2. Direct Path: Bypasses SQL processing for faster loading.

Handling Errors in SQL*Loader

Bad File Example

BADFILE 'error_records.bad'

Discard File Example

DISCARDFILE 'discarded.dsc'
DISCARDMAX 500

Filtering Data in SQL*Loader Control File

Using the WHEN clause to filter records:

INTO TABLE employees
WHEN department = 'IT'
(emp_id, emp_name, department)

Using SQL*Loader with Date Formats

LOAD DATA
INFILE 'data.txt'
INTO TABLE sales
FIELDS TERMINATED BY ','
(sale_id, sale_date DATE 'YYYYMMDD', sale_amount)

Running SQL*Loader in Unix Terminal

sqlldr apps/p_pwd control=load_data.ctl log=load.log

Case Study: Loading a CSV File into an Oracle EBS Table

1. SQL Loader Initialization and Planning in Oracle:

Before diving into the technical details, careful planning is crucial. Consider the following:

• EBS Table Structure: Understand the target EBS table’s columns, data types, constraints (primary keys, foreign keys, not nulls), and any specific requirements (e.g., mandatory columns, seeded data). Knowing the table structure is essential for a successful load. For this example, let’s assume a simplified table named XX_SAMPLE_TABLE:

CREATE TABLE XX_SAMPLE_TABLE (
    HEADER_ID NUMBER PRIMARY KEY,
    CUSTOMER_NAME VARCHAR2(255),
    ORDER_DATE DATE,
    QUANTITY NUMBER,
    PRICE NUMBER,
    PRODUCT_CODE VARCHAR2(50),
    SHIP_TO_ADDRESS VARCHAR2(255),
    BILL_TO_ADDRESS VARCHAR2(255),
    STATUS VARCHAR2(20),
    CREATED_BY VARCHAR2(80), 
    CREATION_DATE DATE,
    LAST_UPDATE_DATE DATE,
    LAST_UPDATED_BY NUMBER
);

• CSV File Format: Ensure your CSV file is properly formatted. Fields should be comma-separated, and text fields should be enclosed in double quotes if they contain commas. The first row should ideally be a header row with column names. For our example, let’s assume sample_date.csv looks like this (first few rows):

CUSTOMER_NAME,ORDER_DATE,QUANTITY,PRICE,PRODUCT_CODE,SHIP_TO_ADDRESS,BILL_TO_ADDRESS,STATUS,CREATED_BY,CREATION_DATE
"Acme Corp",2023-10-26,10,100.00,ABC-123,"123 Main St","456 Oak Ave",Shipped,USER1,2023-10-26
"Beta Inc",2023-10-27,5,200.00,XYZ-789,"789 Elm St","101 Pine Ln",Pending,USER2,2023-10-27

• Data Mapping: Determine how the columns in your CSV file map to the columns in your Oracle table. This is critical for defining the control file.
• Error Handling: Plan for potential errors during the load process (e.g., data type mismatches, constraint violations). SQL*Loader provides options for handling errors, which we’ll cover.
• EBS Context: For Oracle EBS few WHO columns like created_by, creation_date, last_update_date and last_updated-by columns. You might need to populate these with appropriate values during the load. You’ll likely want to use a sequence for the HEADER_ID if it’s a primary key.

2. Creating the SQL Loader Control File (xx_sample_load.ctl) in Oracle:

The control file is the heart of the SQL Loader. It instructs SQL Loader on how to read the data from the CSV file and load it into the Oracle table.

OPTIONS(SKIP=1) -- Skip the header record
LOAD DATA
INFILE 'sample_data.csv'
BADFILE 'sample_data.bad'  -- Records that caused errors
DISCARDFILE 'sample_data.dis'-- Records that were discarded by where clause
LOGFILE 'sample_load.log'  -- Log of the SQL*Loader run
APPEND                     -- Appends data to the table (other options: 
                           -- INSERT, REPLACE, TRUNCATE)
INTO TABLE XX_SAMPLE_TABLE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS            -- Treats missing columns as nulls
(
    HEADER_ID SEQUENCE(XX_SAMPLE_TABLE_SEQ.NEXTVAL),
    CUSTOMER_NAME,
    ORDER_DATE DATE "YYYY-MM-DD", -- Specifies the date format
    QUANTITY,
    PRICE,
    PRODUCT_CODE,
    SHIP_TO_ADDRESS,
    BILL_TO_ADDRESS,
    STATUS,
    CREATED_BY CONSTANT 'Interface', -- Sets a constant value for CREATED_BY
    CREATION_DATE SYSDATE       -- Sets the current date and time
)

Explanation of Control File Elements:

• LOAD_DATE: Starts the data load process.
• INFILE: Specifies the input CSV file.
• BADFILE, DISCARDFILE, LOGFILE: Define the files for error handling and logging. Always use these.
• APPEND: Specifies how to load the data (append, insert, replace, truncate).
• INTO TABLE: Specifies the target table.
• FIELDS TERMINATED BY: Defines the field delimiter.
• OPTIONALLY ENCLOSED BY: Defines the character used to enclose text fields.
• TRAILING NULLCOLS: Handles missing columns in the data file.
• ID SEQUENCE (XX_SAMPLE_TABLE_SEQ.NEXTVAL): Tells SQLLDR to use a sequence to generate unique IDs, essential for EBS and primary keys. Sequence XX_SAMPLE_TABLE_SEQ must be alreay present.
• ORDER_DATE DATE “YYYY-MM-DD”: Specifies the date format in the CSV file.
• CREATED_BY CONSTANT ‘USER1’: Sets the CREATED_BY column to a constant value. Replace ‘Interface’ with the appropriate EBS user.
• CREATION_DATE SYSDATE: Sets the CREATION_DATE to the current system date and time.

3. Creating the Sequence (if needed):

Before running SQL*Loader, create the sequence if your table’s primary key uses one:

CREATE SEQUENCE XX_SAMPLE_TABLE_SEQ
  START WITH 1000
  INCREMENT BY 1
  NOMAXVALUE
  NOCACHE;

4. Running SQL Loader in Oracle Server:

Use the following sqlldr (SQL*Loader) command in your terminal (adjust paths as needed or execute command in the same directory as control file):

sqlldr apps/p_apps_pwd control=xx_sample_load.ctl

Replace p_apps_pwd with your actual EBS credentials.

[appsit@app102 sloader]$ sqlldr apps/Do0_Tyu3 control=xx_sample_load.ctl

SQL*Loader: Release 10.1.0.5.0 - Production on Tue Feb 25 07:58:52 2025

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 1
Commit point reached - logical record count 2
[appsit@app102 sloader]$ cat xx_sample_load.log

SQL*Loader: Release 10.1.0.5.0 - Production on Tue Feb 25 07:58:52 2025

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   xx_sample_load.ctl
Data File:      sample_data.csv
  Bad File:     sample_data.bad
  Discard File: sample_data.dis
 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table XX_SAMPLE_TABLE, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CUSTOMER_NAME                       FIRST     *   ,  O(") CHARACTER
ORDER_DATE                           NEXT     *   ,  O(") DATE YYYY-MM-DD
QUANTITY                             NEXT     *   ,  O(") CHARACTER
PRICE                                NEXT     *   ,  O(") CHARACTER
PRODUCT_CODE                         NEXT     *   ,  O(") CHARACTER
SHIP_TO_ADDRESS                      NEXT     *   ,  O(") CHARACTER
BILL_TO_ADDRESS                      NEXT     *   ,  O(") CHARACTER
STATUS                               NEXT     *   ,  O(") CHARACTER
CREATED_BY                                                CONSTANT
    Value is 'Interface'
CREATION_DATE                                             SYSDATE
HEADER_ID                            NEXT     *   ,  O(") CHARACTER
    SQL string for column : "XX_SAMPLE_TABLE_SEQ.NEXTVAL"


Table XX_SAMPLE_TABLE:
  2 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 149248 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:             2
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Tue Feb 25 07:58:52 2025
Run ended on Tue Feb 25 07:58:52 2025

Elapsed time was:     00:00:00.11
CPU time was:         00:00:00.01

5. Verifying the Data:

After the load completes, check the log file (sample_load.log) for any errors. Then, query the table to verify the data has been loaded correctly:

SQL

SELECT * FROM XX_SAMPLE_TABLE;

6. Advanced Considerations:

• Data Validation: SQL*Loader allows for more complex data validation using WHEN clauses in the control file. This can help catch data inconsistencies before they are loaded into the table.
• Pre-processing: For very complex transformations, it might be easier to pre-process the CSV file using scripting languages like Python or Perl before loading it with SQL*Loader.
• Parallel Loading: For very large files, consider using parallel loading to improve performance.
• EBS Specifics: Be aware of any EBS-specific APIs or procedures that might be required for data loading. Consult your EBS documentation for details. The created_by, creation_date, last_update_date and last_updated-by handling is a good starting point, but other EBS tables may have more complex requirements. Consider using FND_GLOBAL.APPS_INITIALIZE for proper EBS context setup.

Registering SQL*Loader as a Concurrent Program

Step1: Place the SQL*Loader Control file for SQL*Loader Concurrent Program

Oracle Apps R12 has SQL*Loader execution method. If you create a concurrent program executable of SQL*Plus, it will get the control file from the product top bin directory(XXPRD/bin). Also data file name might be changed for every execution of the program. So you need to give the data file name as parameter

OPTIONS(SKIP=1)
LOAD DATA                     
APPEND 
INTO TABLE XX_SAMPLE_TABLE
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS            
(
    CUSTOMER_NAME,
    ORDER_DATE DATE 'YYYY-MM-DD',
    QUANTITY,
    PRICE,
    PRODUCT_CODE,
    SHIP_TO_ADDRESS,
    BILL_TO_ADDRESS,
    STATUS,
    CREATED_BY CONSTANT 'Interface', 
    CREATION_DATE SYSDATE ,
    HEADER_ID "XX_SAMPLE_TABLE_SEQ.NEXTVAL"	
)

ftp the control file xx_sample_load.ctl using winScp/FileZilla into XXONT/bin directory

Step2: Register Concurrent Program Executable for SQL*Loader

Navigate to System Administrator -> Concurrent -> Program -> Executable

Step3: Register Concurrent Program for SQL*Loader

Navigate to System Administrator -> Concurrent -> Program -> Define. Create Concurrent Program defination linking the executable created in earlier step.

Step4: Add Concurrent Program to Request Group for SQL*Loader

Add the SQL*Loader concurrent program “XX Sample Data Loader” to the appropriate request group so that the program can be run or scheduled from a responsibility.

Step5: Run and Validate the Concurrent Program for SQL*Loader

After completion of the program log file will be generated with datail statistics of SQL*Loader data processing in Oracle EBS.

+---------------------------------------------------------------------------+
XX Custom Application: Version : 12.2

Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.

XX_SAMPLE_LOAD: XX Sample Data Loader
+---------------------------------------------------------------------------+

Current system time is 25-FEB-2025 09:00:26

+---------------------------------------------------------------------------+


+-----------------------------
| Starting concurrent program execution...
+-----------------------------

Arguments
------------
/export/home/appsit/sloader/sample_data.csv
------------

SQL*Loader: Release 10.1.0.5.0 - Production on Tue Feb 25 09:00:26 2025

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   /xxsit/app02/appl/fs1/EBSapps/appl/xxpo/12.0.0/bin/xx_sample_load.ctl
Data File:      /export/home/appsit/sloader/sample_data.csv
  Bad File:     /xxsit/app02/appl/fs_ne/inst/XXSIT_app102/logs/appl/conc/out/o457060828.out
  Discard File: /xxsit/app02/appl/fs_ne/inst/XXSIT_app102/logs/appl/conc/out/o457060828.out 
 (Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table XX_SAMPLE_TABLE, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CUSTOMER_NAME                       FIRST     *   ,  O(") CHARACTER            
ORDER_DATE                           NEXT     *   ,  O(") DATE YYYY-MM-DD      
QUANTITY                             NEXT     *   ,  O(") CHARACTER            
PRICE                                NEXT     *   ,  O(") CHARACTER            
PRODUCT_CODE                         NEXT     *   ,  O(") CHARACTER            
SHIP_TO_ADDRESS                      NEXT     *   ,  O(") CHARACTER            
BILL_TO_ADDRESS                      NEXT     *   ,  O(") CHARACTER            
STATUS                               NEXT     *   ,  O(") CHARACTER            
CREATED_BY                                                CONSTANT
    Value is 'Interface'
CREATION_DATE                                             SYSDATE
HEADER_ID                            NEXT     *   ,  O(") CHARACTER            
    SQL string for column : "XX_SAMPLE_TABLE_SEQ.NEXTVAL"


Table XX_SAMPLE_TABLE:
  2 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 149248 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          1
Total logical records read:             2
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Tue Feb 25 09:00:26 2025
Run ended on Tue Feb 25 09:00:27 2025

Elapsed time was:     00:00:00.14
CPU time was:         00:00:00.02



+---------------------------------------------------------------------------+
No completion options were requested.

Output file size: 


+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 25-FEB-2025 09:00:27

+---------------------------------------------------------------------------+

Performance Optimization

• Use DIRECT PATH for faster loads.
• Use Parallel Loading for large datasets.
• Increase READSIZE and BINDSIZE for efficiency.

Troubleshooting Common Issues of SQL Loader in Oracle

SQL*Loader is a powerful tool for bulk loading data into Oracle databases. However, errors can occur due to incorrect configurations, data format issues, or system constraints. Below are some common issues and their solutions.

1. Control File Errors

Issue: Missing or Incorrect Syntax in Control File

• Error Message: SQL*Loader-350: Syntax error at line X
• Cause: Incorrect keywords, misplaced clauses, or missing quotes in the control file.
• Solution:
  • Check for missing INTO TABLE clauses.
  • Ensure correct field delimiters and column mappings.
  • Verify correct use of WHEN, TRAILING NULLCOLS, and other conditions.

2. Input Data Errors

Issue: Data Format Mismatch

• Error Message: ORA-01722: invalid number
• Cause: Trying to load character data into a numeric column.
• Solution:
  • Ensure correct column mappings in the control file.
  • Use INTEGER EXTERNAL for numeric values stored as text.

Issue: Date Format Mismatch

• Error Message: ORA-01861: literal does not match format string
• Cause: Date format in the data file does not match the expected format.
• Solution:
  • Use TO_DATE function in the control file.
  • Example: ORDER_DATE DATE “YYYY-MM-DD”

3. Badfile & Discardfile Issues

Issue: Records Not Loaded

• Error Message: No data loaded, but no clear error message.
• Cause: Data is being rejected due to errors but not displayed in the log.
• Solution:
  • Check the BADFILE and DISCARDFILE for rejected records.
  • Example: BADFILE ‘sample_data.bad‘ DISCARDFILE ‘sample_data.dsc’
  • Use DISCARDMAX to limit discarded records.

4. Performance Issues

Issue: SQL*Loader Running Slowly

• Cause: Using conventional path instead of direct path.
• Solution:
  • Use DIRECT=TRUE to speed up data loading.
  • Increase BINDSIZE and READSIZE for better memory usage.
  • Example: OPTIONS (DIRECT=TRUE, BINDSIZE=1000000)

5. Constraints & Index Errors

Issue: Unique Constraint Violation

• Error Message: ORA-00001: unique constraint violated
• Cause: Duplicate data being inserted into a unique column.
• Solution:
  • Use REPLACE or TRUNCATE mode if overwriting data.
  • Remove duplicates from the input file.

Issue: Foreign Key Constraint Violation

• Error Message: ORA-02291: integrity constraint violated
• Cause: Trying to insert child records without a matching parent.
• Solution:
  • Ensure parent data exists before loading child records.
  • Load parent tables first.

6. File Handling Issues

Issue: Input File Not Found

• Error Message: SQL*Loader-500: Unable to open file
• Cause: Incorrect path or missing file.
• Solution:
  • Verify file path and permissions.
  • Example: INFILE ”sample_data.csv’

Issue: Encoding Problems

• Error Message: Garbled characters in loaded data.
• Cause: Incorrect character set conversion.
• Solution:
  • Specify CHARACTERSET in the control file.
  • Example: CHARACTERSET UTF8

7. Maximum Errors Exceeded

Issue: Job Fails Due to Too Many Errors

• Error Message: SQL*Loader-951: Error count exceeds maximum allowed
• Cause: Too many bad records.
• Solution:
  • Increase the ERRORS parameter.
  • Example: OPTIONS(ERRORS=1000)

Final Tips

Utilize SQL*Loader Options for better performance tuning.

Always check the LOG FILE for detailed error messages.

Use DIRECT PATH for large data loads.

Validate data before loading to reduce errors.

SQL*Loader Quick Reference Guide

Conclusion

SQL*Loader is a powerful and flexible tool for bulk data loading in Oracle databases. Proper configuration, error handling, and optimization techniques help achieve maximum performance.

FAQs