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.
The following is the corresponding delimiter-separated data file:
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:
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.
The following is the corresponding data file containing fixed-width fields:
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.
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.
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.
Using the same fixed-width data file as in the prior fixed-width field example, the resulting rows in the table appear as follows:
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.
The following is the delimiter-separated data file used in this example:
The following table is loaded using the preceding control file and data file:
The resulting rows in the table appear as follows:
Field types with length specification
This control file contains the field-type clauses with the length specification:
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:
The resulting rows in the table appear as follows:
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:
The following is the corresponding data file:
The resulting rows in the table appear as follows:
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:
The following is the content of the dept table used in the SELECT statement:
The following is the corresponding data file:
The resulting rows in the table appear as follows:
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:
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.
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:
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:
The following are the rows loaded into the emp_research and emp_sales tables: