SQL Loader Tutorial


Example 1: Loading Variable length Data

Example 1: demonstartes the following

  • A simple control file identifying one table and three columns to be loaded.
  • Including the data to be loaded in the control file itself, so there is no separate data file.
Control File

The control file is example1.ctl

1)   LOAD DATA
2)   INFILE *
3)   INTO TABLE dept_ldr_trg
4)   FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
5)   (deptno, dname, loc)
6)   BEGINDATA
   123,SALES,"DELHI"
   124,"ACCOUNTING",HYDERABAD
   125,"PACKING",KOLKATTA
   126,FINANCE,"MUMBAI"
   127,"HR",CHENNAI.
   128,"ADMIN",PUNE
   129,"RECRUITMENT","GURGAON"
   ,TRANSPORT,"NOIDA"

Points to note:

  1. The LOAD DATA statement marks the beginning of the control file.
  2. INFILE * specifies that data is found in the control file and not in an external file.
  3. The INTO TABLE statement is required to identify the table to be loaded. Since no option is mentioned, the default option is INSERT. By default SQL* Loader requires the table to be empty before it inserts any records.
  4. TERMINATED BY ',' specifies that the data is terminated by commas.
OPTIONALLY ENCLOSED BY specifies that the data may be optionally enclosed by Quotations.
  1. The names of the columns to be loaded are enclosed in parentheses.  Since no data type is specified, the default is a character of length 255.
  2. BEGINDATA specifies the beginning of the data.

To run this example:

sqlldr scott/tiger control=example1.ctl log=example1.log

SQL Loader loads the dept_ldr_trg table and creates a log file example1.log.
The last record gets rejected and the same can be checked from the bad file and the log file as the table dept_trg_ldr has a NOT NULL constraint on the deptno column.

Example 2 – Loading Fixed format Fields

Example 2: demonstrates the following
  • A separate data file
  • Data conversions
  • Load dates using the data type DATE.
  • Usage of functions like TO_CHAR, TO_DATE, DECODE, UPPER etc
  • Using a sequence to insert unique values.
  • Inserting a constant value

Control File

1)    LOAD DATA
2)    INFILE 'example2.dat'
3)    INTO TABLE emp_ldr_trg
4)    (empno          POSITION(01:04)   INTEGER  EXTERNAL,
5)     ename          POSITION(06:15)   CHAR "UPPER(:ename)",
       job            POSITION(17:25)   CHAR,
       mgr            POSITION(27:30)   INTEGER EXTERNAL,
6)     hiredate       POSITION(63:73)   DATE(11)"DD-MON-YYYY",
       sal            POSITION(32:39)   DECIMAL  EXTERNAL,
7)     comm           POSITION(41:48)   DECIMAL  EXTERNAL "NVL(:comm,0)",
8)     deptno         POSITION(50:51)   CHAR TERMINATED BY ':',
       dname          POSITION(53:62)   CHAR,
9)     region         CONSTANT '31',
10)     time_loaded    "TO_CHAR(SYSDATE,'HH24:MI:SS')",
11)    date_loaded    "TO_DATE(SYSDATE,'DD-MON-YYYY')",
12)    load_seq       "test_ldr_seq.NEXTVAL",
13)    annual_comm    "DECODE((:job),'SALESMAN',(:comm*12),100)"
14)    )

Points to note:

  1. LOAD DATA marks the beginning of the control file.
  2. The name of the data file follows INFILE.
  3. The INTO TABLE statement identifies the table into which data is being loaded.  The table should be empty since we are loading the table in INSERT mode.
  4. Lines 4 to 8 identify a column name and the location of the data in the data file to be loaded into that column. EMPNO, ENAME, JOB, and so on are names of the columns in the table emp_ldr_trg. The data types INTEGER EXTERNAL, CHAR, DECIMAL EXTERNAL identify the data type of the data fields in the file and not the corresponding columns in the emp_ldr_trg table.
  5. The function UPPER is used to insert the employee name in Upper case into the table.
  6. The data to be loaded into the HIREDATE column appears in the format DD-MON-YYYY.
  7. NVL function is used insert 0 as commission in the column COMM if the commission provided is null in the data file.
  8. The department number in the data file is terminated by :
  9. A constant value of 31 is inserted into the REGION column for all the records.
  10. Timestamp is loaded into the TIME_LOADED column. TO_CHAR function is used since the TIME_LOADED column is of data type VARCAHAR2.
  11. SYSDATE is entered into the DATE_LOADED column and TO_DATE function is used since the column is of type DATE.
  12. Unique values are loaded into the LOAD_SEQ column from a sequence.
  13. Usage of DECODE function is shown. If the job of a person is SALESMAN, then the annual commission is commission*12, else the commission is 100.
  14. The column specification should be enclosed in parenthesis.

Data File

Below are a few sample data lines from the file example2.dat.
1111 CLARK      MANAGER   2111 6543.50           10:MARKETING 15-MAR-1989
1112 KING       PRESIDENT      9832.00           40           07-JUN-1976           
1113 MILLER     CLERK     2112  720.00           10:MARKETING 25-JAN-1993   
1114 JONES      MANAGER   2113 5432.75           20:ADMIN     10-AUG-1989      
1115 ALLEN      SALESMAN  2114 1900.00   300.00  30:SALES     05-MAR-1990      
1116 MARTIN     SALESMAN  2114 1385.50  1400.00  30:SALES     22-MAR-1997

To run this example:

sqlldr scott/tiger control=example2.ctl log=example2.log data=example2.dat

All the 6 records get loaded into the table emp_ldr_trg.

Example 3: Loading combined physical records

Example 3 demonstrates the following

  • Combining multiple physical records to form one logical record with CONTINUEIF.
  • Inserting negative numbers.
  • Indicating with REPLACE that the table should be emptied before loading new data.
  • Specifying a discard file in the control file using DISCARDFILE.
  • Specifying the maximum number of discards using DISCARDMAX.
  • Rejecting records due to duplicate values in a unique index or due to invalid data values.

Control File

   LOAD DATA
   INFILE 'example4.dat'
   DISCARDFILE 'example4.dsc'
   DISCARDMAX 999
   REPLACE
   CONTINUEIF THIS (1) = '*'
   INTO TABLE emp_ldr_trg
   (empno          POSITION(01:04)   INTEGER  EXTERNAL,
    ename          POSITION(06:15)   CHAR "UPPER(:ename)",
    job            POSITION(17:25)   CHAR,
    mgr            POSITION(27:30)   INTEGER EXTERNAL,
    hiredate       POSITION(63:73)   DATE(11)"DD-MON-YYYY",
    sal            POSITION(32:39)   DECIMAL  EXTERNAL,
    comm           POSITION(41:48)   DECIMAL  EXTERNAL "NVL(:comm,0)",
    deptno         POSITION(50:51)   CHAR TERMINATED BY ':',
    dname          POSITION(53:62)   CHAR,
    region         CONSTANT '31',
    time_loaded    "TO_CHAR(SYSDATE,'HH24:MI:SS')",
    date_loaded    "TO_DATE(SYSDATE,'DD-MON-YYYY')",
    load_seq       "test_ldr_seq.NEXTVAL",
    annual_comm    "DECODE((:job),'SALESMAN',(:comm*12),100)"
    )

Points to note:

  1. DISCARDFILE specifies the name of the discard file as example3.dsc
  2. DISCARDMAX specifies that a maximum of 999 discards are allowed before terminating the run.
  3. REPLACE specifies that if there is data in the table being loaded, SQL*Loader should delete that data before loading new data.
  4. CONTINUEIF specifies that if an asterisk is found in column 1of the current record, then the next physical record after that record should be appended to it to form the logical record. Note that column 1 in each physical record should then contain either an asterisk or a non-data value

Data File

Below are a few sample data lines from the file example3.dat.
*1111 CLARK     
 MANAGER   2111 6543.50           10:MARKETING 15-MAR-1989
*1112 KING      
 PRESIDENT      9832.00           40           07-JUN-1976           
*1113 MILLER    
 CLERK     2112  720.00           10:MARKETING 25-JAN-1993   
*1114 JONES     
 MANAGER   2113 5432.75           20:ADMIN     10-AUG-1989      
*1115 ALLEN     
 SALESMAN  2114 1900.00   300.00  30:SALES     05-MAR-1990      
*     MARTIN    
 SALESMAN  2114 1385.50  1400.00  30:SALES     22-MAR-1997

To run this example:

sqlldr scott/tiger control=example3.ctl log=example3.log data=example3.dat

All the records except for the last one get loaded into the table emp_ldr_trg.
MARTIN’s record gets rejected since we have a not null constraint on the empno column of the table and the empno in this record is null.

Though a discard file name is mentioned, a discard file does not get created since there are no records which are rejected due to conditions not satisfied in the control file.

The log file lists the records which are rejected.

Example 4 Loading data into multiple tables

Example4 demonstrates the following

  • Using a WHEN clause.
  • Loading into multiple tables.
  • Deriving multiple logical records from a single physical record.
  • Loading the same field (EMPNO) into multiple tables.
  • Use of SKIP option to indicate the number of records to skip
  • Indicating with APPEND that the records should get appended in the table without deleting any records.

Control File

   LOAD DATA
   INFILE 'example4.dat'
   BADFILE 'example4.bad'
   DISCARDFILE 'example4.dsc'
   APPEND
   INTO TABLE emp_ldr_trg
   WHEN deptno != '  '
   (empno          POSITION(01:04)   INTEGER  EXTERNAL,
    ename          POSITION(06:15)   CHAR "UPPER(:ename)",
    job            POSITION(17:25)   CHAR,
    mgr            POSITION(27:30)   INTEGER EXTERNAL,
    hiredate       POSITION(63:73)   DATE(11)"DD-MON-YYYY",
    sal            POSITION(32:39)   DECIMAL  EXTERNAL,
    comm           POSITION(41:48)   DECIMAL  EXTERNAL "NVL(:comm,0)",
    deptno         POSITION(50:51)   CHAR TERMINATED BY ':',
    dname          POSITION(53:62)   CHAR,
    region         CONSTANT '31',
    time_loaded    "TO_CHAR(SYSDATE,'HH24:MI:SS')",
    date_loaded    "TO_DATE(SYSDATE,'DD-MON-YYYY')",
    load_seq       "test_ldr_seq.NEXTVAL",
    annual_comm    "DECODE((:job),'SALESMAN',(:comm*12),100)"
    )
   INTO TABLE dept_ldr_trg  
   WHEN deptno != '  '
   (deptno         POSITION(50:51)   CHAR TERMINATED BY ':',
    dname          POSITION(53:62)   CHAR)

Points to note

1.    The WHEN clause signifies that the records should get loaded only if the DEPTNO is not null in the data file.
2.    The records with DEPTNO as null are rejected and a discard file gets created with those records.
3.    EMP_LDR_TRG and the DEPT_LDR_TRG tables get loaded with the corresponding data.
4.    Use of SKIP option to indicate the number of records to skip.
5.    Using APPEND option to append the records to the table without deleting any existing records.

Data File
Below are a few sample data lines from the file example4.dat.
ENO  ENAME      JOB       MGR  SAM      COM      DN DNAME     HIREDATE
1116 MARK       MANAGER   2115 6543.50           10:MARKETING 15-MAR-1989
1117 LUTHER     PRESIDENT      9832.00           40           07-JUN-1976           
1118 NANCY      CLERK     2116  720.00           10:MARKETING 25-JAN-1993   
1119 JONNY      MANAGER   2117 5432.75           20:ADMIN     10-AUG-1989      
1119 MONY       MANAGER   2117 5432.75                        10-AUG-1989   
To run this example:

sqlldr scott/tiger control=example4.ctl log=example4.log data=example4.dat skip=1

All the records except for the last one get loaded into the tables emp_ldr_trg and dept_ldr_trg.
MONY’s record gets rejected since the DEPTNO is null.

The discard file gets created with the records which are discarded. In this case with MONY’s record.
at 1:11:00 PM
Labels: , , , , ,