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)