SQL LOADER

SQL loader is oracle utility use for load data from external sources.

To perform the insert using SQL loader we need a control file. Simply a file with .ctl extension contains the parameter for load i.e INFILE  file name from which use for data load .The table format in which data have to load ,the path of the discard file[Contains  records which are not meet load criteria ] and bad file [Contains records are not match the format of columns in table ].How to insert data in table whether INSERT[simply insert data in empty table] APPEND[append rows with the existing data] ,TRUNCATE[truncate table first  and then load data] or REPLACE[delete data first from the  table and then insert new data] .If we have to load data with different formats  we have to create control file different according to data format.

If we are unable to use proper clues the number of bad or discard records increase .first we have to analyze the data which to be load and then create control file according to requirement some time to load data in proper format need to use a bunch of clues provided by the ORACLE in this article u will find a verity of control file to load data form different format files 

First how to run SQL Loader from command prompt

sqlldr scott/tiger control=multiplefile.ctl log=multiplefile.log Discard=archiver.dat bad= archiver.bad  bindsize=10000000 readsize=1000000000 rows=5000

*First  we load a have field data to be loaded is separated by anydelimiter “|”

Example of the data file:-

v7347|ebusi|sec-2|gurgaon|758475848

h476|hnl|sec-9|pune|647357364

g637|lokp|sec-8|pune|534537363

Example of Control file :-

load data

infile 'archvefile1.txt'

append into table archiver_data

fields terminated by '|'

(COMP_ID  ,

 COMP_NAME,

 COMP_ADD ,

 CITY     ,

 PHONE_NO )

* Load data from the file having data separated by the tab.

 X'09’ specifies to load field data separated by tab and new line terminated by eliminator “|”."str '|\n'" record separator is here is either a  “|” or a line feed.

i.e

v7347   ebusi   sec-2   gurgaon 758475848 34|

h476     hnl    sec-9   pune    647357364 |

g637    lokp    sec-8   pune    534537363|

control file format tabsepratedata.ctl

infile 'archvefile1.txt' "str '|\n'"

append into table archiver_data

fields terminated by X'09'

(COMP_ID  ,

 COMP_NAME,

 COMP_ADD ,

 CITY     ,

 PHONE_NO )

* To load data from multiple tables specifies the multiple INFILE clues

i.e

infile 'archvefile1.txt'

infile 'archvefile2.txt'

 append into table archiver_data

fields terminated by X'09'

(COMP_ID  ,

 COMP_NAME,

 COMP_ADD ,

 CITY     ,

 PHONE_NO )

If we want to skip some records then SQL Loader provide the OPTION

SKIP . lets we have  a file having a row with the header(column name) ,no need to insert in table hence we can skip by using  SKIP=1

In next file we load data using fixed length

Example of data file:-

123456789…………………………………….character count

Id          name        address city        Phone No-----àheader row

v7347   ebusi        sec-2   gurgaon      758475848

h476     hnl.mtphkl    sec-9   Cherapungi    647357364

g637    lokp         sec-8    pune         534537363

for insert the value of first column we specifies the position POSTION(1:5) length of the record having maximum length.

Using Fixed position is faster if we use delaminates; loader scans the data to find the delimiter. If the gap between records is large replace the gap with comma it improves performance.

Control file example :-

OPTIONS (SKIP=2)

load data

infile 'archvefile1.txt'

append into table archiver_data

 (COMP_ID POSITION(1:6)  ,

 COMP_NAME POSITION(7:14),

 COMP_ADD POSITION(15:24),

 CITY    POSITION(25:40) ,

 PHONE_NO POSITION(41:51))

specific column then this complete row treated as rejected i.eusing csv file I am insert six records in a table in csv file record is like nx890,,, then its treated as rejected records to avoid this situation SQL loader have option TRAILING NULLCOLS , which treat this type of If records in the file to be load contains  no record for the records as null value. And insert the value null in the table.

Remember if all records are null then SQL loader never load that rows it show the counts in Bad file with note :all records of row are null.

i:e

Record 1: Rejected - Error on table ARCHIVER_DATA, column COMP_ID.

Column not found before end of logical record (use TRAILING NULLCOLS)

Example of data file:-

v7347\n0code,ebusi,,sec-2,758475848,,,,

nx890,,,

h476\n0No,hnl,Cherapungi,,,647357364

ag637\n0code,lokp,sec-8,,,534537363,,, output

in the above data file we have null column ,null rows and the we can break

first record into two lines. To avoid the null rows we specifies TRAILING NULLCOLS and for split the recodes in two lines we use ‘\\n\’ .Notice \\nnot “\n” because “\n” converts  into a new line.

Example of control file:-

load data

infile 'archvefile1.txt'

truncate  into table  archiver_data

fields terminated by ','

TRAILING NULLCOLS

(COMP_ID  "replace(:comp_id,'\\n',chr(10))" ,

 COMP_NAME ,

 COMP_ADD ,

 CITY     ,

 PHONE_NO )

COMP_ID

--------------------

v7347

0code

h476

0No

ag637

0code

WHEN option is use for insert the conditional records [record which meets the given condition] in given example we want to insert records

In which city name is start with latter g in archiver_data table.

load data

infile 'archvefile1.txt'

truncate  into table  archiver_data

WHEN (15) = 'g'

TRAILING NULLCOLS

(COMP_ID POSITION(1:6)  ,

 COMP_NAME POSITION(7:14),

 COMP_ADD POSITION(15:24),

 CITY    POSITION(25:40) ,

 PHONE_NO POSITION(41:51))

Concatenate the one column to other or concatenate the value with any column i.e have to concatenate std code with phone number , we can use oracle function to insert the data , constant values using CONSTANT clues and can add sequence in a column of table as given in below example.

load data

infile 'archvefilenew.txt'

truncate  into table  archiver_data

 TRAILING NULLCOLS

(COMP_ID  CONSTANT 'njkl' ,

 COMP_NAME POSITION(7:14) "substr(:COMP_NAME,1,2)",

 COMP_ADD POSITION(15:24),

 CITY    POSITION(25:40) ,

 PHONE_NO POSITION(42:45) "0124||:PHONE_NO",

COMP_NO  "com_seq.nextval")