8 Most Common SQL Loader Control File Examples

In this article, I will share different examples for SQL loader (sqlldr) control file. SQL loader control file is used to load data from CSV or flat data file to Oracle tables. It usually has extension .ctl. In the control file, we define the structure for the data file and the target table in which we want to load data with some conditions. Below are the 8 most common SQL Loader control file examples. Please have a look.

The following is the SQL Loader control file example to load data from CSV file in which fields are separated with Comma and no column header is present. As I described in my previous article.

1. SQL Loader Control File Example to load CSV file (comma separated) having no column header

LOAD DATA
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE DATE "dd/mm/yyyy",
SAL,
COMM,
DEPTNO
)

Below is the example of the control file to skip the first row because the CSV file is containing column header row, which obviously you don't want to load into table. This control file is having OPTIONS (SKIP=1) command to skip the first row.

2. SQL Loader Control File Example to Skip First Row

OPTIONS (SKIP=1)
LOAD DATA
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE DATE "dd/mm/yyyy",
SAL,
COMM,
DEPTNO
)

Below is another SQL Loader control file example to load data into multiple Oracle tables by specifying when clause. Suppose you want that when field Job is CLERK in CSV then into EMP table and when field Job is MGR in CSV then Employees table.

3. SQL Loader Control File Example to Load Data into Multiple Tables upon Condition

LOAD DATA
APPEND
INTO TABLE EMP
when JOB = 'CLERK'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE DATE "dd/mm/yyyy",
SAL,
COMM,
DEPTNO
)
INTO TABLE EMPLOYEES
when JOB = 'MGR'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE DATE "dd/mm/yyyy",
SAL,
COMM,
DEPTNO
)

The above example will check Job field value to upload data accordingly but suppose what if you have another column in CSV which is describing you the record type but it is not needed to load into table. Then use the below approach to load data conditionally by specifying that column as filler. In the below example that filler column is at first position means the first column of every row in CSV file and we have given the name rectype, any name can be given.

4. SQL Loader Control File Example Using Filler Column

LOAD DATA
APPEND
INTO TABLE EMP
when rectype = 'CLERK'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
rectype filler POSITION(1) CHAR,
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE DATE "dd/mm/yyyy",
SAL,
COMM,
DEPTNO
)
INTO TABLE EMPLOYEES
when rectype = 'MGR'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
rectype filler POSITION(1) CHAR,
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE DATE "dd/mm/yyyy",
SAL,
COMM,
DEPTNO
)

Another example below to generate the sequence (1, 2, 3) for every record and will store into the table column. Assuming you have a column in table SEQ_NO.

5. SQL Loader Control File Example to Generate Sequence number

LOAD DATA
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
SEQ_NO SEQUENCE,
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE DATE "dd/mm/yyyy",
SAL,
COMM,
DEPTNO
)

Use Oracle functions for columns while loading the data into tables. Below is the example to trim the field values before inserting.

6. SQL Loader Control File Example Using Functions

LOAD DATA
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO,
ENAME "TRIM(:ENAME)",
JOB,
MGR,
HIREDATE DATE "dd/mm/yyyy",
SAL,
COMM,
DEPTNO
)

Specify fields size also specify the format for date fields. Check the below control file example.

7. SQL Loader Control File Example to Specify field size and field formats.

LOAD DATA
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO,
ENAME,
JOB CHAR(10),
MGR,
HIREDATE DATE "DD/MM/YYYY HH:MI:SS AM",
SAL,
COMM,
DEPTNO
)

Below is the example to specify a constant value for a column in the table while uploading the data. Suppose we have a column Status in the table EMP and while uploading data we want to store OK in it.

8. SQL Loader Control File Example Using Constant Values

LOAD DATA
APPEND
INTO TABLE EMP
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMPNO,
ENAME,
JOB CHAR(10),
MGR,
HIREDATE DATE "DD/MM/YYYY HH:MI:SS AM",
SAL,
COMM,
DEPTNO,
STATUS CONSTANT "OK"
)

These are the most frequently used control files examples I shared. Hope it will be helpful for you. Thanks.