Oracle SQL*Loader FAQ

$Date: 17-Dec-1999 $
$Revision: 1.22 $
$Author: Frank Naudé $

Topics

        sqlldr orauser/passwd control=loader.ctl

        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

   LOAD DATA
   INFILE *
   INTO TABLE load_delimited_data
   FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
   TRAILING NULLCOLS
   (  data1,
      data2
   )
   BEGINDATA
   11111,AAAAAAAAAA
   22222,"A,B,C,D,"

   LOAD DATA
   INFILE *
   INTO TABLE load_positional_data
   (  data1 POSITION(1:5),
      data2 POSITION(6:15)
   )
   BEGINDATA
   11111AAAAAAAAAA
   22222BBBBBBBBBB

   LOAD DATA
   INFILE *
   INTO TABLE modified_data
   (  rec_no                      "my_db_sequence.nextval",
      region                      CONSTANT '31',
      time_loaded                 "to_char(SYSDATE, 'HH24:MI')",
      data1        POSITION(1:5)  ":data1/100",
      data2        POSITION(6:15) "upper(:data2)",
      data3        POSITION(16:22)"to_date(:data3, 'YYMMDD')"
   )
   BEGINDATA
   11111AAAAAAAAAA991201
   22222BBBBBBBBBB990112

   LOAD DATA
   INFILE 'mail_orders.txt'
   BADFILE 'bad_orders.txt'
   APPEND
   INTO TABLE mailing_list
   FIELDS TERMINATED BY ","
   (  addr,
      city,
      state,
      zipcode,
      mailing_addr   "decode(:mailing_addr, null, :addr, :mailing_addr)",
      mailing_city   "decode(:mailing_city, null, :city, :mailing_city)",
      mailing_state
   )

   LOAD DATA
   INFILE *
   REPLACE
   INTO TABLE emp
        WHEN empno != ' '
   ( empno  POSITION(1:4)   INTEGER EXTERNAL,
     ename  POSITION(6:15)  CHAR,
     deptno POSITION(17:18) CHAR,
     mgr    POSITION(20:23) INTEGER EXTERNAL
   )
   INTO TABLE proj
        WHEN projno != ' '
   (  projno POSITION(25:27) INTEGER EXTERNAL,
      empno  POSITION(1:4)   INTEGER EXTERNAL
   )

   LOAD DATA
   APPEND
   INTO TABLE my_selective_table
   WHEN (01) <> 'H' and (01) <> 'T' and (30:37) = '19991217'
   (
      region              CONSTANT '31',
      service_key         POSITION(01:11)   INTEGER EXTERNAL,
      call_b_no           POSITION(12:29)   CHAR
   )

• CONCATENATE: - use when SQL*Loader should add the same number of physical records together to form one logical record.

• CONTINUEIF - use if a condition indicates that multiple records should be treated as one, eg '#' in line 1.

1. A very simple but easily overlooked hint, do not have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slowdown load times even with ROWS= set to a high value.

2. Add the following option in the command line: DIRECT=TRUE. This will effectively bypass most of the RDBMS processing. However, there are cases when you can't use direct load. Refer to chapter 8 on Oracle server Utilities manual.

3. Turn off database logging by specifying the UNRECOVERABLE option. This option can only be used with direct data loads.

4. Run multiple load jobs concurrently.

        set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
        spool oradata.txt
        select col1 || ',' || col2 || ',' || col3
        from   tab1
        where  col2 = 'XYZ';
        spool off

        rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
        declare
           fp utl_file.file_type;
        begin
           fp := utl_file.fopen('c:\oradata','tab1.txt','w');
           utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
           utl_file.fclose(fp);
        end;
        /

You might also want to investigate third party tools like Platinum FastUnload or TOAD.