SQL Loader Part 2

SQL LOADER is a very powerful tool that lets you load data from a delimited or position based data file into Oracle tables. We have received many questions regarding SQL LOADER features from many users. Here is the brief explanation on the same.

Please note that the basic knowledge of SQL LOADER is required to understand this article.

This article covers the below topics:

1.Load multiple data files into a single table

2.Load a single data file into multiple tables

3.Skip a column while loading using “FILLER” and Load field in the delimited data file into two different columns in a table using “POSITION”

4.Usage of BOUNDFILLER

5.Load the same record twice into a single table

6.Using WHEN to selectively load the records into the table

7.Run SQLLDR from SQL PLUS

8.Default path for Discard, bad and log files

1)Load multiple files into a single table:

SQL LOADER lets you load multiple data files at once into a single table. But all the data files should be of the same format.

Here is a working example:

Say you have a table named EMP which has the below structure:

Column Data Type
emp_num Number
emp_name Varchar2(25)
department_num Number
department_name Varchar2(25)

 

You are trying to load the below comma delimited data files named eg.dat and eg1.dat:

eg.dat:

7369,SMITH,7902,Accounting

7499,ALLEN,7698,Sales

7521,WARD,7698,Accounting

7566,JONES,7839,Sales

7654,MARTIN,7698,Accounting

 

eg1.dat:

1234,Tom,2345,Accounting

3456,Berry,8976,Accounting

 

The Control file should be built as below:

 

LOAD DATA

INFILE ‘eg.dat’ — File 1

INFILE ‘eg1.dat’ — File 2

APPEND

INTO TABLE emp

FIELDS TERMINATED BY “,”

( emp_num, emp_name, department_num, department_name )

 

2)Load a single file into multiple tables:

SQL Loader lets you load a single data file into multiple tables using “INTO TABLE” clause.

Here is a working example:

Say you have two tables named EMP and DEPT which have the below structure:

Table Column Data Type
EMP emp_num Number
EMP emp_name Varchar2(25)
DEPT department_num Number
DEPT department_name Varchar2(25)

 

You are trying to load the below comma delimited data file named eg.dat which has columns Emp_num and emp_name that need to be loaded into table EMP and columns department_num and department_name that need to be loaded into table DEPT using a single CTL file here.

eg.dat:

7369,SMITH,7902,Accounting

7499,ALLEN,7698,Sales

7521,WARD,7698,Accounting

7566,JONES,7839,Sales

7654,MARTIN,7698,Accounting

 

The Control file should be built as below:

LOAD DATA

INFILE ‘eg.dat’

APPEND

INTO TABLE emp

FIELDS TERMINATED BY “,”

( emp_num, emp_name )

INTO TABLE dept

FIELDS TERMINATED BY “,”

(department_num, department_name)

You can further use WHEN clause to selectively load the records into the tables which will be explained later in this article.

 

3)Skip a column while loading using “FILLER” and Load field in the delimited data file into two different columns in a table using “POSITION”

SQL LOADER lets to skip unwanted fields in the data file by using the “FILLER” clause. Filler was introduced in Oracle 8i.

SQL LOADER also lets you load the same field into two different columns of the table.

If the data file is position based, loading the same field into two different columns is pretty straight forward. You can use Position (start_pos:end_pos) keyword

If the data file is a delimited file and it has a header included in it, then this can be achieved by referring the field preceded with “:” eg description “(:emp_name)”.

If the data file is delimited file without a header included in it, Position (start_pos:end_pos) or “(:field)” will not work. This can be achieved using POSITION (1) clause which takes you to the beginning of the record.

 

Here is a Working Example:

The requirement here is to load the field emp_name in the data field into two columns – emp_name and description of the table EMP. Here is the Working Example:

 

Say you have a table named EMP which has the below structure:

Column Data Type
emp_num Number
emp_name Varchar2(25)
description Varchar2(25)
department_num Number
department_name Varchar2(25)

 

You are trying to load the below comma delimited data file named eg.dat which has 4 fields that need to be loaded into 5 columns of the table EMP.

eg.dat:

7369,SMITH,7902,Accounting

7499,ALLEN,7698,Sales

7521,WARD,7698,Accounting

7566,JONES,7839,Sales

7654,MARTIN,7698,Accounting

 

Control File:

LOAD DATA

INFILE ‘eg.dat’

APPEND

INTO TABLE emp

FIELDS TERMINATED BY “,”

(emp_num,

emp_name,

desc_skip FILLER POSITION(1),

description,

department_num,

department_name)

Explanation on how SQL LOADER processes the above CTL file:

The first field in the data file is loaded into column emp_num of table EMP

The second field in the data file is loaded into column emp_name of table EMP

The field desc_skip enables SQL LOADER to start scanning the same record it is at from the beginning because of the clause POSITION(1) . SQL LOADER again reads the first delimited field and skips it as directed by “FILLER” keyword.

Now SQL LOADER reads the second field again and loads it into description column of the table EMP.

SQL LOADER then reads the third field in the data file and loads into column department_num of table EMP

Finally the fourth field is loaded into column department_name of table EMP.

4)Usage of BOUNDFILLER

BOUNDFILLER is available with Oracle 9i and above and can be used if the skipped column’s value will be required later again.
Here is an example:

The requirement is to load first two fields concatenated with the third field as emp_num into table emp and Fourth field as Emp_name

 

Data File:

1,15,7369,SMITH

1,15,7499,ALLEN

1,15,7521,WARD

1,18,7566,JONES

1,20,7654,MARTIN

 

The requirement can be achieved using the below Control File:

LOAD DATA

INFILE ‘C:eg.dat’

APPEND

INTO TABLE EMP

FIELDS TERMINATED BY “,”

(

Rec_skip BOUNDFILLER,

tmp_skip BOUNDFILLER,

Emp_num “(:Rec_skip||:tmp_skip||:emp_num)”,

Emp_name

)

 

5)Load the same record twice into a single table:

SQL Loader lets you load record twice using POSITION clause but you have to take into account whether the constraints defined on the table allow you to insert duplicate rows.

 

Below is the Control file:

 

LOAD DATA

INFILE ‘eg.dat’

APPEND

INTO TABLE emp

FIELDS TERMINATED BY “,”

( emp_num, emp_name, department_num, department_name )

INTO TABLE emp

FIELDS TERMINATED BY “,”

(emp_num POSITION(1),emp_name,department_num,department_name)

 

SQL LOADER processes the above control file this way:

First “INTO TABLE” clause loads the 4 fields specified in the first line of the data file into the respective columns (emp_num, emp_name, department_num, department_name)

Field scanning does not start over from the beginning of the record when SQL LOADER encounters the second INTO TABLE clause in the CTL file. Instead, scanning continues where it left off. Statement “emp_num POSITION(1)” in the CTL file forces the SQL LOADER to read the same record from the beginning and loads the first field in the data file into emp_num column again. The remaining fields in the first record of the data file are again loaded into respective columns emp_name, department_num, department_name. Thus the same record can be loaded multiple times into the same table using “INTO TABLE” clause.

 

6)Using WHEN to selectively load the records into the table

WHEN clause can be used to direct SQL LOADER to load the record only when the condition specified in the WHEN clause is TRUE. WHEN statement can have any number of comparisons preceded by AND. SQL*Loader does not allow the use of OR in the WHEN clause.

Here is a working example which illustrates how to load the records into 2 tables EMP and DEPT based on the record type specified in the data file.

The below is delimited data file eg.dat which has the first field as the record type. The requirement here is to load all the records with record type = 1 into table EMP and all the records with record type = 2 into table DEPT and record with record type =3 which happens to be the trailer record should not be loaded.

1,7369,SMITH

2,7902,Accounting

1,7499,ALLEN

2,7698,Sales

1,7521,WARD

2,7698,Accounting

1,7566,JONES

2,7839,Sales

1,7654,MARTIN

2,7698,Accounting

3,10

Control File:

LOAD DATA

INFILE ‘eg.dat’

APPEND

INTO TABLE emp

WHEN (01) = ‘1’

FIELDS TERMINATED BY “,”

( rec_skip filler POSITION(1),emp_num , emp_name )

INTO TABLE dept

WHEN (01) = ‘2’

FIELDS TERMINATED BY “,”

(rec_skip filler POSITION(1),department_num,

department_name )

Let’s now see how SQL LOADER processes the CTL file:

SQL LOADER loads the records into table EMP only when first position (01) of the record which happens to be the record type is ‘1’ as directed by command

INTO TABLE emp

WHEN (01) = ‘1’

If condition When (01) = ‘1’ holds true for the current record, then SQL LOADER gets to the beginning of the record as directed by command POSITION(1) and skips the first field which is the record type.

It then loads the second field into emp_num and third field into emp_name column in the table EMP.

SQL LOADER loads the records into table DEPT only when first position (01) of the record which happens to be the record type is ‘2’ as directed by the commands –

INTO TABLE dept

WHEN (01) = ‘2’

If condition When (01) = ‘2’ holds true for the current record, then SQL LOADER gets to the beginning of the record as directed by command POSITION(1) and skips the first field which is the record type.

It then loads the second field into department_num and third field into department_name columns in the table DEPT.

The records with record type = ‘3’ are not loaded into any table.

Thus you can selectively loads the necessary records into various tables using WHEN clause.

 

7)Run SQLLDR from SQL PLUS

SQL LOADER can be invoked from SQL PLUS using “host” command as shown below:

host sqlldr userid= username/password@host control = C:eg.ctl log = eg.log

 

8)Default path for Discard, bad and log files

If bad and discard file paths are not specified in the CTL file and if this SQL Loader is registered as a concurrent program, then they will be created in the directory where the regular Concurrent programs’ output files reside. You can also find the paths where the discard and bad files have been created in the log file of the SQL LOADER concurrent request.

18 Comments

  1. balagangadharreddy
    May 27, 2011 @ 12:31:12

    very good documents

    Reply

  2. Preeti
    Jun 13, 2011 @ 15:37:32

    nice documents.. helped to understand better….

    Reply

  3. Narendar Reddy.Bijjam
    Jun 25, 2011 @ 07:34:21

    This code is very useful to every oracle apps student

    Reply

  4. Khushbu
    Jun 29, 2011 @ 14:35:43

    Excellent document to refer….

    Reply

  5. ksatya
    Aug 03, 2011 @ 16:57:23

    hai u gave best of best notes regarding the SQl * loader it helps us a lot hope we are expecting this type of articles

    Reply

  6. Phani Adusumilli
    Aug 09, 2011 @ 19:51:26

    Can we load line number in the file to the table, so that we can refer join by record level when we are loading multiple tables without using the sequence number. Since the sequence number keeps on the incrementing. I want some think like record # 1,2,3,… for each file i load.

    — Thanks,
    Phani Adusumilli

    Reply

  7. Phani Adusumilli
    Aug 09, 2011 @ 23:11:30

    Used SEQUENCE(1,1) gives record count from 1, for 0 use SEQUENCE(0,1)

    — Phani Adusumilli

    Reply

  8. Ram_POsam
    Sep 16, 2011 @ 10:10:40

    SUPERB DOCUMENTS IT IS VERY USEFUL FOR ME.THANK U……….

    Reply

  9. Shyam
    Nov 11, 2011 @ 18:28:20

    One question : I have the following scenario. Can you please tell me how the control file should be like? It is a pipe delimited file.
    Fields to be loaded –
    emp_num
    emp_name
    status_date
    dept_num
    dept_name
    location_id
    location_name

    dept_num and dept_name should have the same values as emp_num and emp_name. Eg data:

    123|David|09081999|10|NewYork

    345|Peter|08122001|20|Paris

    I have to re-read fields 1 and 2 and then continue from where I left off.

    Please help.

    Reply

  10. debi
    Feb 06, 2012 @ 15:17:51

    Excellent Document for oracle apps student who really want regarding SQL LOQADER in short span of time….

    Reply

  11. Prasad
    Feb 28, 2012 @ 02:48:33

    Thanks for the detailed information, wondering if there is a way to load the filename using SQL loader

    Reply

  12. Abhinay
    Mar 04, 2012 @ 15:20:38

    Nice one. I have a question. Using ‘WHEN’ is causing me an issue in SQL loader. I have input files say for example
    Type,Data,Status
    Value1,Testing Data,
    Value2,Testing Data2, A
    Value2,Testing Data2, B
    Value3, Testing Data3,

    Now I want to skip rows from input file when Type = ‘Value2′ and Status =’A’

    I am not able to do this. I have used like “WHEN Type = ‘Value2′ AND Status ‘A'” condition
    but the sql loader rejects all records of Type ‘Value2′ …

    Can you please help?

    Reply

  13. Raja Dutta
    Jul 19, 2012 @ 13:27:21

    How to Skip Columns in a Delimited Datafile using SQLLoader

    Suppose I have a data file:

    1,ABCD,200.00
    2,WXYZ,300.00

    my table structure is
    tab(c1,c2)

    Now I want to insert data from 2nd column onwards i.e I want to skip column 1 in data file.
    c1 c2
    ————————-
    ABCD 200.00
    WXYZ 300.00

    Any idea how to achieve this?

    Reply

  14. Murali Putttaparthi
    Oct 18, 2012 @ 09:34:20

    There are 5 fields in the Table. The input file contains only 3 fields. Does our control file need to have all the 5 fields in theTable or only those 3 fields is enough in the control file. Please any one can share with me the possible solution.(If possible the Code also).

    Reply

  15. vinay sawant
    Dec 13, 2012 @ 14:15:07

    To,
    raja datta

    LOAD DATA

    INFILE ‘eg.dat’

    APPEND

    INTO TABLE TAB

    FIELDS TERMINATED BY “,”

    (desc_skip FILLER POSITION(0),
    C1,
    C2,
    )

    Reply

  16. VAMSI
    Oct 11, 2013 @ 14:56:09

    Nice document
    thanks a lot yar….

    Reply

  17. Yogesh
    Oct 13, 2013 @ 23:49:15

    Hi Pruthvi,
    This is Yogesh,my suggestion is ,why cant we start a seperate section for Real time interview questions.
    Suppose a guy went to interview and he lost it.if he post the questions in the Real time interview questions so some one can see and answer those questions..as Oracle Apps is rare subject, straight away we don’t get answers in google.
    I think this might be use-full for oracle apps freshers,job seekers and Experienced guys.

    I am sorry if any thing i made mistake.

    regards,
    Yogesh
    yogeshwar419@gmail.com

    Reply

  18. Rahul Chauhan
    Apr 22, 2014 @ 10:25:07

    Hi All,

    i came across a control file where there is no file name given, from where it is loading the data into table.
    File goes something like–

    OPTIONS(ROWS=10000,ERRORS=2000000,SKIP=1)
    LOAD DATA
    APPEND INTO TABLE XXMAS.PROMISE_DATES
    FIELDS TERMINATED BY ‘|’ OPTIONALLY ENCLOSED BY ‘”‘
    TRAILING NULLCOLS
    (
    ORDER_NUMBER “LTRIM(RTRIM(:ONT_ORDER_NUMBER))”,
    SOURCE_SYSTEM “LTRIM(RTRIM(:SOURCE_SYSTEM))”,
    ITEM_NUMBER “LTRIM(RTRIM(:ITEM_NUMBER))”,
    NEW_PROMISE_DATE Date “YYYYMMDD”,
    PO_NUMBER “LTRIM(RTRIM(:PO_NUMBER))”,
    PO_LINE_NUMBER_CHAR “LTRIM(RTRIM(:PO_LINE_NUMBER_CHAR))”,
    ORDERED_QUANTITY,
    INBOUND_ORG_ID,
    CONV_REQUEST_ID Constant “-1″,
    FILE_REQUEST_ID Constant “-1″,
    CONV_STATUS Constant “FILE”,
    CREATION_DATE Sysdate,
    CREATED_BY Constant “-1″,
    LAST_UPDATE_DATE Sysdate,
    LAST_UPDATED_BY Constant “-1″,
    LAST_UPDATE_LOGIN Constant “-1″,
    ERROR_FLAG Constant ‘N’,
    LOADED_FLAG Constant ‘N’
    )

    Reply

Leave a Reply