SQL*Loader Control File Reference

Control File Contents

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

To create the SQL*Loader control file, use a text editor such as vi or xemacs.create. 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 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:

1. This is how comments are entered in a control file. 
2. The LOAD DATA statement tells SQL*Loader that this is the beginning of a new data load.
3. The INFILE clause specifies the name of a datafile containing data that you want to load. 
4. The BADFILE parameter specifies the name of a file into which rejected records are placed. 
5. The DISCARDFILE parameter specifies the name of a file into which discarded records are placed. 
6. The APPEND parameter is one of the options you can use when loading data into a table that is not empty. 
   
   
   To load data into a table that is empty, you would use the INSERT parameter. 
7. The INTO TABLE clause allows you to identify tables, fields, and datatypes. It defines the relationship between records in the datafile and tables in the database. 
8. The WHEN clause specifies one or more field conditions. SQL*Loader decides whether or not to load the data based on these field conditions.
9. The TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns. 
10. The remainder of the control file contains the field list, which provides information about column formats in the table being loaded. 

INFILE  Clause:

The following list shows different ways you can specify INFILE syntax:

Data contained in the control file itself:

INFILE  *

Data contained in a file named foo with a default extension of .dat:

INFILE  foo

Data contained in a file named datafile.dat with a full path specified:

INFILE 'c:/topdir/subdir/datafile.dat'

Specifying Multiple Data Files:

To load data from multiple datafiles in one SQL*Loader run, use an INFILE statement for each datafile. Datafiles need not have the same file processing options, although the layout of the records must be identical. For example, two files could be specified with completely different file processing options strings, and a third could consist of data in the control file.

You can also specify a separate discard file and bad file for each datafile. In such a case, the separate bad files and discard files must be declared immediately after each datafile name. For example, the following excerpt from a control file specifies four datafiles with separate bad and discard files:

INFILE  mydat1.dat  BADFILE  mydat1.bad  DISCARDFILE mydat1.dis 
INFILE  mydat2.dat 
INFILE  mydat3.dat  DISCARDFILE  mydat3.dis 
INFILE  mydat4.dat  DISCARDMAX  10 0

• For mydat1.dat, both a bad file and discard file are explicitly specified. Therefore both files are created, as needed.
• For mydat2.dat, neither a bad file nor a discard file is specified. Therefore, only the bad file is created, as needed. If created, the bad file has the default filename and extension mydat2.bad. The discard file is notcreated, even if rows are discarded.
• For mydat3.dat, the default bad file is created, if needed. A discard file with the specified name (mydat3.dis) is created, as needed.
• For mydat4.dat, the default bad file is created, if needed. Because the DISCARDMAX option is used, SQL*Loader assumes that a discard file is required and creates it with the default name mydat4.dsc.

Identifying Data in the Control File with BEGINDATA

If the data is included in the control file itself, then the INFILE clause is followed by an asterisk rather than a filename. The actual data is placed in the control file after the load configuration specifications.

Specify the BEGINDATA parameter before the first data record. The syntax is:

BEGINDATA 
data

Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file:

load data

 infile *

 replace

 into table departments

 (  dept     position (02:05) char(4),

    deptname position (08:27) char(20)

 )

begindata

COSC  COMPUTER SCIENCE

ENGL  ENGLISH LITERATURE

MATH  MATHEMATICS

POLY  POLITICAL SCIENCE

Keep the following points in mind when using the BEGINDATA parameter:

• If you omit the BEGINDATA parameter but include data in the control file, SQL*Loader tries to interpret your data as control information and issues an error message. If your data is in a separate file, do not use theBEGINDATA parameter.
• Do not use spaces or other characters on the same line as the BEGINDATA parameter, or the line containing BEGINDATA will be interpreted as the first line of data.
• Do not put comments after BEGINDATA, or they will also be interpreted as data.

Table-Specific Loading Method

When you are loading a table, you can use the INTO TABLE clause to specify a table-specific loading method (INSERT, APPEND, REPLACE, or TRUNCATE) that applies only to that table. That method overrides the global table-loading method. The global table-loading method is INSERT, by default, unless a different method was specified before any INTO TABLE clauses. The following sections discuss using these options to load data into empty and nonempty tables.

Loading Data into Empty Tables

If the tables you are loading into are empty, use the INSERT option.

INSERT
This is SQL*Loader's default method. It requires the table to be empty before loading. SQL*Loader terminates with an error if the table contains rows.

Loading Data into Nonempty Tables

If the tables you are loading into already contain data, you have three options:

• APPEND
• REPLACE
• TRUNCATE

APPEND
If data already exists in the table, SQL*Loader appends the new rows to it. If data does not already exist, the new rows are simply loaded. You must have SELECT privilege to use the APPEND option.

REPLACE

With REPLACE, all rows in the table are deleted and the new data is loaded. The table must be in your schema, or you must have DELETE privilege on the table

TRUNCATE

The SQL TRUNCATE statement quickly and efficiently deletes all rows from a table or cluster, to achieve the best possible performance. For the TRUNCATE statement to operate, the table's referential integrity constraints must first be disabled. If they have not been disabled, SQL*Loader returns an error.

INTO table Clause:

Among its many functions, the INTO TABLE clause allows you to specify the table into which you load data. To load multiple tables, you include one INTO TABLE clause for each table you wish to load.

To begin an INTO TABLE clause, use the keywords INTO TABLE, followed by the name of the Oracle table that is to receive the data.

The syntax is as follows:

intotab ::= 
  INTO TABLE name [ ( { PARTITION name | SUBPARTITION name } ) ] 
  { INSERT | REPLACE | TRUNCATE | APPEND }

The table must already exist. The table name should be enclosed in double quotation marks if it is the same as any SQL or SQL*Loader reserved keyword, if it contains any special characters, or if it is case sensitive.

INTO TABLE scott."CONSTANT"
INTO TABLE scott."Constant" 
INTO TABLE scott."-CONSTANT" 

WHEN Clause:

You can choose to load or discard a logical record by using the WHEN clause to test a condition in the record.

The WHEN clause appears after the table name and is followed by one or more field conditions. 

The syntax for field_condition is as follows:

fld_cond ::= 

  [(] 
  { full_fieldname | pos_spec } operator { 'char_string' | X'hex_string' | BLANKS } 
  [)] AND
  [(]
  { full_fieldname | pos_spec } operator { 'char_string' | X'hex_string' | BLANKS }
  [)]...

For example, the following clause indicates that any record with the value "q" in the fifth column position should be loaded:

WHEN (5) = 'q' 

A WHEN clause can contain several comparisons, provided each is preceded by AND. Parentheses are optional, but should be used for clarity with multiple comparisons joined by AND, for example:

WHEN (deptno = '10') AND (job = 'SALES') 

TRAILING NULLCOLS Clause

The TRAILING NULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.

For example, consider the following data:

10 Accounting 


Assume that the preceding data is read with the following control file and the record ends after dname:

INTO TABLE dept 

    TRAILING NULLCOLS 

( deptno CHAR TERMINATED BY " ", 

dname  CHAR TERMINATED BY WHITESPACE, 

loc    CHAR TERMINATED BY WHITESPACE 

) 


In this case, the remaining loc field is set to null. Without the TRAILING NULLCOLS clause, an error would be generated due to missing data.

Field List Reference

This chapter describes the field-list portion of the SQL*Loader control file.

Field List Contents

The field-list portion of a SQL*Loader control file provides information about fields being loaded, such as position, datatype, conditions, and delimiters.

Example Field List Section of Sample Control File

     (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"
    )

SYSDATE sets the column to the current system date
POSITION specifies the position of a data field.
The NULLIF clause is one of the clauses that can be used to specify field conditions. In this sample, the field is being compared to blanks, using the BLANKS parameter.
The TERMINATED BY WHITESPACE clause is one of the delimiters it is possible to specify for a field.
The ENCLOSED BY clause is another possible field delimiter.

Examples of Using POSITION
siteid  POSITION (*) SMALLINT 
siteloc POSITION (*) INTEGER 


If these were the first two column specifications, siteid would begin in column 1, and siteloc would begin in the column immediately following.
ename  POSITION (1:20)  CHAR 
empno  POSITION (22-26) INTEGER EXTERNAL 
allow  POSITION (*+2)   INTEGER EXTERNAL TERMINATED BY "/" 


Column ename is character data in positions 1 through 20, followed by column empno, which is presumably numeric data in columns 22 through 26. Column allow is offset from the next position (27) after the end ofempno by +2, so it starts in column 29 and continues until a slash is encountered.