The SQL*Loader control file is a text file that contains data definition language (DDL) instructions. DDL is used to control the following aspects of a SQL*Loader session:
Where SQL*Loader will find the data to load
How SQL*Loader expects that data to be formatted
How SQL*Loader will be configured (memory management, rejecting records, interrupted load handling, and so on) as it loads the data
How SQL*Loader will manipulate the data being loaded
See SQL*Loader Syntax Diagrams for syntax diagrams of the SQL*Loader DDL.
To create the SQL*Loader control file, use a text editor such as vi or xemacs.
In general, the control file has three main sections, in the following order:
Session-wide information
Table and field-list information
Input data (optional section)
Example 9-1 shows a sample control file.
Example 9-1 Sample Control File
1 -- This is a sample control file 2 LOAD DATA 3 INFILE 'sample.dat' 4 BADFILE 'sample.bad' 5 DISCARDFILE 'sample.dsc' 6 APPEND 7 INTO TABLE emp 8 WHEN (57) = '.' 9 TRAILING NULLCOLS 10 (hiredate SYSDATE, deptno POSITION(1:2) INTEGER EXTERNAL(2) NULLIF deptno=BLANKS, job POSITION(7:14) CHAR TERMINATED BY WHITESPACE NULLIF job=BLANKS "UPPER(:job)", mgr POSITION(28:31) INTEGER EXTERNAL TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS, ename POSITION(34:41) CHAR TERMINATED BY WHITESPACE "UPPER(:ename)", empno POSITION(45) INTEGER EXTERNAL TERMINATED BY WHITESPACE, sal POSITION(51) CHAR TERMINATED BY WHITESPACE "TO_NUMBER(:sal,'$99,999.99')", comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%' ":comm * 100" )
In this sample control file, the numbers that appear to the left would not appear in a real control file. They are keyed in this sample to the explanatory notes in the following list:
This is how comments are entered in a control file. See "Comments in the Control File".
The LOAD DATA statement tells SQL*Loader that this is the beginning of a new data load. See SQL*Loader Syntax Diagrams for syntax information.
The INFILE clause specifies the name of a data file containing the data you want to load. See "Specifying Data Files".
The BADFILE clause specifies the name of a file into which rejected records are placed. See "Specifying the Bad File".
The DISCARDFILE clause specifies the name of a file into which discarded records are placed. See "Specifying the Discard File".
The APPEND clause is one of the options you can use when loading data into a table that is not empty. See "Loading Data into Nonempty Tables".
To load data into a table that is empty, you would use the INSERT clause. See "Loading Data into Empty Tables".
The INTO TABLE clause enables you to identify tables, fields, and data types. It defines the relationship between records in the data file and tables in the database. See "Specifying Table Names".
The WHEN clause specifies one or more field conditions. SQL*Loader decides whether to load the data based on these field conditions. See "Loading Records Based on a Condition".
The TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns. See "Handling Short Records with Missing Data".
The remainder of the control file contains the field list, which provides information about column formats in the table being loaded. See SQL*Loader Field List Reference for information about that section of the control file.