Link to home
Start Free TrialLog in
Avatar of avi_ny
avi_ny

asked on

Sql loader

Index   Search   Add FAQ   Ask Question    

Oracle SQL*Loader FAQ
$Date: 09-Jul-2005 $
$Revision: 1.30 $
$Author: Frank Naudé $


Topics
What is SQL*Loader and what is it used for?
How does one use the SQL*Loader utility?
Is there a SQL*Unloader to download data to a flat file?
Can one load variable and fix length data records?
Can one skip header records load while loading?
Can one modify data as it loads into the database?
Can one load data into multiple tables at once?
Can one selectively load only the records that one need?
Can one skip certain columns while loading data?
How does one load multi-line records?
How can one get SQL*Loader to COMMIT only at the end of the load file?
Can one improve the performance of SQL*Loader?
What is the difference between the conventional and direct path loader?
How does one use SQL*Loader to load images, sound clips and documents?
Where can one get more info about SQL*Loader?

--------------------------------------------------------------------------------
Back to Oracle FAQ Index
--------------------------------------------------------------------------------

What is SQL*Loader and what is it used for?
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.

Back to top of file
--------------------------------------------------------------------------------

How does one use the SQL*Loader utility?
One can load data into an Oracle database by using the sqlldr (sqlload on some platforms) utility. Invoke the utility without arguments to get a list of available parameters. Look at the following example:
        sqlldr scott/tiger control=loader.ctl
This sample control file (loader.ctl) will load an external data file containing delimited data:
        load data
          infile 'c:\data\mydata.csv'
              into table emp
          fields terminated by "," optionally enclosed by '"'              
          ( empno, empname, sal, deptno )
The mydata.csv file may look like this:
      10001,"Scott Tiger", 1000, 40
      10002,"Frank Naude", 500, 20
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
Back to top of file
--------------------------------------------------------------------------------

Is there a SQL*Unloader to download data to a flat file?
Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file:
        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
Alternatively use the UTL_FILE PL/SQL package:
        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 SQLWays from Ispirer Systems, TOAD from Quest, or ManageIT Fast Unloader from CA to help you unload data from Oracle.


Back to top of file
--------------------------------------------------------------------------------

Can one load variable and fix length data records?
Yes, look at the following control file examples. In the first we will load delimited data (variable length):
   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,"
If you need to load positional data (fixed length), look at the following control file example:
   LOAD DATA
   INFILE *
   INTO TABLE load_positional_data
   (  data1 POSITION(1:5),
      data2 POSITION(6:15)
   )
   BEGINDATA
   11111AAAAAAAAAA
   22222BBBBBBBBBB
Back to top of file
--------------------------------------------------------------------------------

Can one skip header records load while loading?
One can skip header records or continue an interrupted load (for example if you run out of space) by specifying the "SKIP n" keyword. "n" specifies the number of logical rows to skip. Look at this example:
   LOAD DATA (SKIP 5)
   INFILE *
   INTO TABLE load_positional_data
   (  data1 POSITION(1:5),
      data2 POSITION(6:15)
   )
   BEGINDATA
   11111AAAAAAAAAA
   22222BBBBBBBBBB
If you are continuing a multiple table direct path load, you may need to use the CONTINUE_LOAD clause instead of the SKIP parameter. CONTINUE_LOAD allows you to specify a different number of rows to skip for each of the tables you are loading.

Back to top of file
--------------------------------------------------------------------------------

Can one modify data as it loads into the database?
Data can be modified as it loads into the Oracle Database. Note that this only applies for the conventional load path and not for direct path loads.
   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
   )
Back to top of file
--------------------------------------------------------------------------------

Can one load data into multiple tables at once?
Look at the following control file:
   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
   )
Back to top of file
--------------------------------------------------------------------------------

Can one selectively load only the records that one need?
Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
   LOAD DATA
   INFILE  'mydata.dat' BADFILE  'mydata.bad' DISCARDFILE 'mydata.dis'
   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
   )
Back to top of file
--------------------------------------------------------------------------------

Can one skip certain columns while loading data?
One cannot use POSTION(x:y) with delimited data. Luckily, from Oracle 8i one can specify FILLER columns. FILLER columns are used to skip columns/fields in the load file, ignoring fields that one does not want. Look at this example:
        LOAD DATA
        TRUNCATE INTO TABLE T1
        FIELDS TERMINATED BY ','
        ( field1,
          field2 FILLER,
          field3
        )
Back to top of file
--------------------------------------------------------------------------------

How does one load multi-line records?
One can create one logical record from multiple physical records using one of the following two clauses:

CONCATENATE: - use when SQL*Loader should combine 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. by having a '#' character in column 1.

Back to top of file
--------------------------------------------------------------------------------

How can get SQL*Loader to COMMIT only at the end of the load file?
One cannot, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=.

Back to top of file
--------------------------------------------------------------------------------

Can one improve the performance of SQL*Loader?
A very simple but easily overlooked hint is not to have any indexes and/or constraints (primary key) on your load tables during the load process. This will significantly slow down load times even with ROWS= set to a high value.

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.

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

Run multiple load jobs concurrently.


Back to top of file
--------------------------------------------------------------------------------

How does one use SQL*Loader to load images, sound clips and documents?
SQL*Loader can load data from a "primary data file", SDF (Secondary Data file - for loading nested tables and VARRAYs) or LOBFILE. The LOBFILE method provides an easy way to load documents, images and audio clips into BLOB and CLOB columns. Look at this example:
Given the following table:

CREATE TABLE image_table (
        image_id   NUMBER(5),
        file_name  VARCHAR2(30),
        image_data BLOB);
Control File:
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
 image_id   INTEGER(5),
 file_name  CHAR(30),
 image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg

Back to top of file
--------------------------------------------------------------------------------

What is the difference between the conventional and direct path loader?
The conventional path loader essentially loads the data by using standard INSERT statements. The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files. More information about the restrictions of direct path loading can be obtained from the Utilities Users Guide.

Back to top of file
--------------------------------------------------------------------------------

Where can one get more info about SQL*Loader?
Using the Oracle Bulk Loader

Solutions to common SQL*Loader questions


Back to top of file

 HOME | ASK QUESTION | ADD FAQ | SEARCH | E-MAIL US  

ASKER CERTIFIED SOLUTION
Avatar of Acton Wang
Acton Wang
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
is there a question here?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial