EDB*Loader control file examples v16
The following are some examples of control files and their corresponding data files.
Delimiter-separated field data file
This control file uses a delimiter-separated data file that appends rows to the emp
table. The APPEND
clause is used to allow inserting additional rows into the emp
table.
LOAD DATA INFILE 'emp.dat' BADFILE 'emp.bad' APPEND INTO TABLE emp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( empno, ename, job, mgr, hiredate, sal, deptno, comm )
The following is the corresponding delimiter-separated data file:
9101,ROGERS,CLERK,7902,17-DEC-10,1980.00,20 9102,PETERSON,SALESMAN,7698,20-DEC-10,2600.00,30,2300.00 9103,WARREN,SALESMAN,7698,22-DEC-10,5250.00,30,2500.00 9104,"JONES, JR.",MANAGER,7839,02-APR-09,7975.00,20
The use of the TRAILING NULLCOLS
clause allows you to omit the last field supplying the comm
column from the first and last records. The comm
column is set to null for the rows inserted from these records.
Double quotation marks surround the value JONES, JR.
in the last record since the comma delimiter character is part of the field value.
This query displays the rows added to the table after the EDB*Loader session:
SELECT * FROM emp WHERE empno > 9100;
empno| ename | job | mgr| hiredate | sal | comm |deptno ------+-----------+---------+----+-------------------+-------+--------+------ 9101| ROGERS | CLERK |7902| 17-DEC-10 00:00:00|1980.00| | 20 9102| PETERSON | SALESMAN|7698| 20-DEC-10 00:00:00|2600.00| 2300.00| 30 9103| WARREN | SALESMAN|7698| 22-DEC-10 00:00:00|5250.00| 2500.00| 30 9104| JONES, JR.| MANAGER |7839| 02-APR-09 00:00:00|7975.00| | 20 (4 rows)
Fixed-width field data file
This control file loads the same rows into the emp
table. It uses a data file containing fixed-width fields. The FIELDS TERMINATED BY
and OPTIONALLY ENCLOSED BY
clauses are absent. Instead, each field includes the POSITION
clause.
LOAD DATA INFILE 'emp_fixed.dat' BADFILE 'emp_fixed.bad' APPEND INTO TABLE emp TRAILING NULLCOLS ( empno POSITION (1:4), ename POSITION (5:14), job POSITION (15:23), mgr POSITION (24:27), hiredate POSITION (28:38), sal POSITION (39:46), deptno POSITION (47:48), comm POSITION (49:56) )
The following is the corresponding data file containing fixed-width fields:
9101ROGERS CLERK 790217-DEC-10 1980.0020 9102PETERSON SALESMAN 769820-DEC-10 2600.0030 2300.00 9103WARREN SALESMAN 769822-DEC-10 5250.0030 2500.00 9104JONES, JR.MANAGER 783902-APR-09 7975.0020
Single physical record data file – RECORDS DELIMITED BY clause
This control file loads the same rows into the emp
table but uses a data file with one physical record. Terminate each record loaded as a row in the table using a semicolon (;
). The RECORDS DELIMITED BY
clause specifies this value.
LOAD DATA INFILE 'emp_recdelim.dat' BADFILE 'emp_recdelim.bad' APPEND INTO TABLE emp FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' RECORDS DELIMITED BY ';' TRAILING NULLCOLS ( empno, ename, job, mgr, hiredate, sal, deptno, comm )
The following is the corresponding data file. The content is a single physical record in the data file. The record delimiter character is included following the last record, that is, at the end of the file.
9101,ROGERS,CLERK,7902,17-DEC-10,1980.00,20,;9102,PETERSON,SALESMAN,7698,20-DEC-10, 2600.00,30,2300.00;9103,WARREN,SALESMAN,7698,22-DEC-10,5250.00,30,2500.00;9104,"JONES, JR.",MANAGER,7839,02-APR-09,7975.00,20,;
FILLER clause
This control file uses the FILLER
clause in the data fields for the sal
and comm
columns. EDB*Loader ignores the values in these fields and sets the corresponding columns to null.
LOAD DATA INFILE 'emp_fixed.dat' BADFILE 'emp_fixed.bad' APPEND INTO TABLE emp TRAILING NULLCOLS ( empno POSITION (1:4), ename POSITION (5:14), job POSITION (15:23), mgr POSITION (24:27), hiredate POSITION (28:38), sal FILLER POSITION (39:46), deptno POSITION (47:48), comm FILLER POSITION (49:56) )
Using the same fixed-width data file as in the prior fixed-width field example, the resulting rows in the table appear as follows:
SELECT * FROM emp WHERE empno > 9100;
empno| ename | job | mgr| hiredate | sal | comm |deptno ------+-----------+---------+----+-------------------+-------+--------+------ 9101| ROGERS | CLERK |7902| 17-DEC-10 00:00:00| | | 20 9102| PETERSON | SALESMAN|7698| 20-DEC-10 00:00:00| | | 30 9103| WARREN | SALESMAN|7698| 22-DEC-10 00:00:00| | | 30 9104| JONES, JR.| MANAGER |7839| 02-APR-09 00:00:00| | | 20 (4 rows)
BOUNDFILLER clause
This control file uses the BOUNDFILLER
clause in the data fields for the job
and mgr
columns. EDB*Loader ignores the values in these fields and sets the corresponding columns to null in the same manner as the FILLER
clause. However, unlike columns with the FILLER
clause, you can use columns with the BOUNDFILLER
clause in an expression, as shown for column jobdesc
.
LOAD DATA INFILE 'emp.dat' BADFILE 'emp.bad' APPEND INTO TABLE empjob FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( empno, ename, job BOUNDFILLER, mgr BOUNDFILLER, hiredate FILLER, sal FILLER, deptno FILLER, comm FILLER, jobdesc ":job || ' for manager ' || :mgr" )
The following is the delimiter-separated data file used in this example:
9101,ROGERS,CLERK,7902,17-DEC-10,1980.00,20 9102,PETERSON,SALESMAN,7698,20-DEC-10,2600.00,30,2300.00 9103,WARREN,SALESMAN,7698,22-DEC-10,5250.00,30,2500.00 9104,"JONES, JR.",MANAGER,7839,02-APR-09,7975.00,20
The following table is loaded using the preceding control file and data file:
CREATE TABLE empjob ( empno NUMBER(4) NOT NULL CONSTRAINT empjob_pk PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), jobdesc VARCHAR2(25) );
The resulting rows in the table appear as follows:
SELECT * FROM empjob;
empno | ename | job | mgr | jobdesc -------+------------+-----+-----+--------------------------- 9101 | ROGERS | | | CLERK for manager 7902 9102 | PETERSON | | | SALESMAN for manager 7698 9103 | WARREN | | | SALESMAN for manager 7698 9104 | JONES, JR. | | | MANAGER for manager 7839 (4 rows)
Field types with length specification
This control file contains the field-type clauses with the length specification:
LOAD DATA INFILE 'emp_fixed.dat' BADFILE 'emp_fixed.bad' APPEND INTO TABLE emp TRAILING NULLCOLS ( empno CHAR(4), ename CHAR(10), job POSITION (15:23) CHAR(9), mgr INTEGER EXTERNAL(4), hiredate DATE(11) "DD-MON-YY", sal DECIMAL EXTERNAL(8), deptno POSITION (47:48), comm POSITION (49:56) DECIMAL EXTERNAL(8) )
Note
You can use the POSITION
clause and the fieldtype(length)
clause individually or in combination as long as each field definition contains at least one of the two clauses.
The following is the corresponding data file containing fixed-width fields:
9101ROGERS CLERK 790217-DEC-10 1980.0020 9102PETERSON SALESMAN 769820-DEC-10 2600.0030 2300.00 9103WARREN SALESMAN 769822-DEC-10 5250.0030 2500.00 9104JONES, JR. MANAGER 783902-APR-09 7975.0020
The resulting rows in the table appear as follows:
SELECT * FROM emp WHERE empno > 9100;
empno| ename | job | mgr| hiredate | sal | comm |deptno ------+-----------+---------+----+-------------------+-------+--------+------ 9101| ROGERS | CLERK |7902| 17-DEC-10 00:00:00|1980.00| | 20 9102| PETERSON | SALESMAN|7698| 20-DEC-10 00:00:00|2600.00| 2300.00| 30 9103| WARREN | SALESMAN|7698| 22-DEC-10 00:00:00|5250.00| 2500.00| 30 9104| JONES, JR.| MANAGER |7839| 02-APR-09 00:00:00|7975.00| | 20 (4 rows)
NULLIF clause
This example uses the NULLIF
clause on the sal
column to set it to null for employees of job MANAGER
. It also uses the clause on the comm
column to set it to null if the employee isn't a SALESMAN
and isn't in department 30
. In other words, a comm
value is accepted if the employee is a SALESMAN
or is a member of department 30
.
The following is the control file:
LOAD DATA INFILE 'emp_fixed_2.dat' BADFILE 'emp_fixed_2.bad' APPEND INTO TABLE emp TRAILING NULLCOLS ( empno POSITION (1:4), ename POSITION (5:14), job POSITION (15:23), mgr POSITION (24:27), hiredate POSITION (28:38), sal POSITION (39:46) NULLIF job = 'MANAGER', deptno POSITION (47:48), comm POSITION (49:56) NULLIF job <> 'SALESMAN' AND deptno <> '30' )
The following is the corresponding data file:
9101ROGERS CLERK 790217-DEC-10 1980.0020 9102PETERSON SALESMAN 769820-DEC-10 2600.0030 2300.00 9103WARREN SALESMAN 769822-DEC-10 5250.0030 2500.00 9104JONES, JR. MANAGER 783902-APR-09 7975.0020 9105ARNOLDS CLERK 778213-SEP-10 3750.0030 800.00 9106JACKSON ANALYST 756603-JAN-11 4500.0040 2000.00 9107MAXWELL SALESMAN 769820-DEC-10 2600.0010 1600.00
The resulting rows in the table appear as follows:
SELECT empno, ename, job, NVL(TO_CHAR(sal),'--null--') "sal", NVL(TO_CHAR(comm),'--null--') "comm", deptno FROM emp WHERE empno > 9100;
empno | ename | job | sal | comm | deptno -------+------------+----------+----------+----------+------- 9101 | ROGERS | CLERK | 1980.00 | --null-- | 20 9102 | PETERSON | SALESMAN | 2600.00 | 2300.00 | 30 9103 | WARREN | SALESMAN | 5250.00 | 2500.00 | 30 9104 | JONES, JR. | MANAGER | --null-- | --null-- | 20 9105 | ARNOLDS | CLERK | 3750.00 | 800.00 | 30 9106 | JACKSON | ANALYST | 4500.00 | --null-- | 40 9107 | MAXWELL | SALESMAN | 2600.00 | 1600.00 | 10 (7 rows)
Note
The sal
column for employee JONES, JR.
is null since the job is MANAGER
.
The comm
values from the data file for employees PETERSON
, WARREN
, ARNOLDS
, and MAXWELL
are all loaded into the comm
column of the emp
table since these employees are either SALESMAN
or members of department 30
.
The comm
value of 2000.00
in the data file for employee JACKSON
is ignored, and the comm
column of the emp
table is set to null. This employee isn't a SALESMAN
or a member of department 30
.
SELECT statement in a field expression
This example uses a SELECT
statement in the expression of the field definition to return the value to load into the column:
LOAD DATA INFILE 'emp_fixed.dat' BADFILE 'emp_fixed.bad' APPEND INTO TABLE emp TRAILING NULLCOLS ( empno POSITION (1:4), ename POSITION (5:14), job POSITION (15:23) "(SELECT dname FROM dept WHERE deptno = :deptno)", mgr POSITION (24:27), hiredate POSITION (28:38), sal POSITION (39:46), deptno POSITION (47:48), comm POSITION (49:56) )
The following is the content of the dept
table used in the SELECT
statement:
SELECT * FROM dept;
deptno | dname | loc ---------+------------+--------- 10 | ACCOUNTING | NEW YORK 20 | RESEARCH | DALLAS 30 | SALES | CHICAGO 40 | OPERATIONS | BOSTON (4 rows)
The following is the corresponding data file:
9101ROGERS CLERK 790217-DEC-10 1980.0020 9102PETERSON SALESMAN 769820-DEC-10 2600.0030 2300.00 9103WARREN SALESMAN 769822-DEC-10 5250.0030 2500.00 9104JONES, JR. MANAGER 783902-APR-09 7975.0020
The resulting rows in the table appear as follows:
SELECT * FROM emp WHERE empno > 9100;
empno| ename | job | mgr| hiredate | sal | comm |deptno ------+-----------+---------+----+-------------------+-------+--------+------ 9101| ROGERS | RESEARCH|7902| 17-DEC-10 00:00:00|1980.00| | 20 9102| PETERSON | SALES |7698| 20-DEC-10 00:00:00|2600.00| 2300.00| 30 9103| WARREN | SALES |7698| 22-DEC-10 00:00:00|5250.00| 2500.00| 30 9104| JONES, JR.| RESEARCH|7839| 02-APR-09 00:00:00|7975.00| | 20 (4 rows)
Note
The job
column contains the value from the dname
column of the dept
table returned by the SELECT
statement instead of the job name from the data file.
Multiple INTO TABLE clauses
This example uses multiple INTO TABLE
clauses. For this example, two empty tables are created with the same data definition as the emp
table. The following CREATE TABLE
commands create these two empty tables without inserting rows from the original emp
table:
CREATE TABLE emp_research AS SELECT * FROM emp WHERE deptno = 99; CREATE TABLE emp_sales AS SELECT * FROM emp WHERE deptno = 99;
This control file contains two INTO TABLE
clauses. Without an APPEND
clause, it uses the default operation of INSERT
. For this operation, the tables emp_research
and emp_sales
must be empty.
LOAD DATA INFILE 'emp_multitbl.dat' BADFILE 'emp_multitbl.bad' DISCARDFILE 'emp_multitbl.dsc' INTO TABLE emp_research WHEN (47:48) = '20' TRAILING NULLCOLS ( empno POSITION (1:4), ename POSITION (5:14), job POSITION (15:23), mgr POSITION (24:27), hiredate POSITION (28:38), sal POSITION (39:46), deptno CONSTANT '20', comm POSITION (49:56) ) INTO TABLE emp_sales WHEN (47:48) = '30' TRAILING NULLCOLS ( empno POSITION (1:4), ename POSITION (5:14), job POSITION (15:23), mgr POSITION (24:27), hiredate POSITION (28:38), sal POSITION (39:46), deptno CONSTANT '30', comm POSITION (49:56) "ROUND(:comm + (:sal * .25), 0)" )
The WHEN
clauses specify that when the field designated by columns 47 through 48 contains 20
, the record is inserted into the emp_research
table. When that same field contains 30
, the record is inserted into the emp_sales
table. If neither condition is true, the record is written to the discard file emp_multitbl.dsc
.
The CONSTANT
clause is given for column deptno
, so the specified constant value is inserted into deptno
for each record. When the CONSTANT
clause is used, it must be the only clause in the field definition other than the column name to which the constant value is assigned.
Column comm
of the emp_sales
table is assigned a SQL expression. Expressions can reference column names by prefixing the column name with a colon character (:
).
The following is the corresponding data file:
9101ROGERS CLERK 790217-DEC-10 1980.0020 9102PETERSON SALESMAN 769820-DEC-10 2600.0030 2300.00 9103WARREN SALESMAN 769822-DEC-10 5250.0030 2500.00 9104JONES, JR. MANAGER 783902-APR-09 7975.0020 9105ARNOLDS CLERK 778213-SEP-10 3750.0010 9106JACKSON ANALYST 756603-JAN-11 4500.0040
The records for employees ARNOLDS
and JACKSON
contain 10
and 40
in columns 47 through 48, which don't satisfy any of the WHEN
clauses. EDB*Loader writes these two records to the discard file, emp_multitbl.dsc
, with the following content:
9105ARNOLDS CLERK 778213-SEP-10 3750.0010 9106JACKSON ANALYST 756603-JAN-11 4500.0040
The following are the rows loaded into the emp_research
and emp_sales
tables:
SELECT * FROM emp_research;
empno | ename | job | mgr | hiredate | sal | comm | deptno -------+------------+---------+------+--------------------+---------+------+------- 9101 | ROGERS | CLERK | 7902 | 17-DEC-10 00:00:00 | 1980.00 | | 20.00 9104 | JONES, JR. | MANAGER | 7839 | 02-APR-09 00:00:00 | 7975.00 | | 20.00 (2 rows)
SELECT * FROM emp_sales;
empno | ename | job | mgr | hiredate | sal | comm | deptno -------+----------+----------+------+--------------------+---------+---------+-------- 9102 | PETERSON | SALESMAN | 7698 | 20-DEC-10 00:00:00 | 2600.00 | 2950.00 | 30.00 9103 | WARREN | SALESMAN | 7698 | 22-DEC-10 00:00:00 | 5250.00 | 3813.00 | 30.00 (2 rows)