SQL*LOADER

This example illustrates how to insert data from a text file to a table in the HR schema using SQL loader.

The data file is named as “records.dat”.
The controlfile file is named as “records.ctl”.
The logfile is named as “records.log”.

1)Connect to the HR schema and create a tables as “TEST”.


SQL> connect hr/hr
SQL> create table test(ID number, DEPTID number, NAME varchar2(10));

2)Using vi editor create a file as “/u01/app/oracle/records.dat”.
  and enter some data into it.


1200,100,ROCK
1201,101,PAPER
1202,102,SCISSORS
1203,103,ALPHA
1204,104,BRAVO
1205,105,CHARLE

3)Now create a controlfile as “/u01/app/oracle/records.ctl”



LOAD DATA
INFILE   ‘/u01/app/oracle/records.dat’
BADFILE    ‘/u01/app/oracle/records.bad’
DISCARDFILE    ‘u01/app/oracle/records.dsc’
INTO TABLE test
INSERT
FIELDS TERMINATED BY “,”
TRAILING NULLCOLS
( ID INTEGER EXTERNAL,
DEPTID INTEGER EXTERNAL,
NAME CHAR)


EXPLANATION

*LOAD DATA – load the data

*INFILE – from the file records.dat

*BADFILE – It contains all the records that are rejected due to
incorrect format or error.

*DISCARDFILE – This file contains the list of all the rejected
records if any due to selection criteria.

*INTO TABLE – Insert the data into the table.

*FIELDS TERMINATED BY “,” – Means the data in the text file is
separated by “,”.

*TRAILING NULLCOLS – It means that any fields that are missing
at the end of a record will be treated as null.


4)Issue the following command at the oracle prompt.


[oracle@ora ~]# sqlldr hr/hr control=/u01/app/oracle/records.ctl log=data.log