Oracle ETL SQL * Loader Tutorial

Check this out for Oracle ETL SQL * Loader tutorial

Check the following link for whole set of explanation at Oracle

Check for the expert section
Check for filtering data into multiple tables


Here we intend to beginner tutorial of SQL* loader with the help of a simple table to load data from a text file

Run the SQL on SQL loader or toad which ever preferred.

create table(
No varchar2,
ename varchar2,
marks number)

Create the following file in a folder

create load_stud_etl1.txt

NO,NAME,MARK
1,Ripal,99
2,Krish,88
3,Radhe,77
4,abcde,66

create load_test_etl1.ctl file as

LOAD DATA
INFILE 'load_stud_etl1.txt'
BADFILE 'load_stud_etl.bad'
DISCARDFILE 'load_stud_etl.dsc'
INSERT INTO TABLE "TEST_ETL1"
--TRUNCATE
FIELDS TERMINATED BY '',"
TRAILING NULLCOLS
(no,
eNAME OPTIONALLY ENCLOSED BY '"',
mark OPTIONALLY ENCLOSED BY '"'
)


create load_stud.bat bat file

SQLLDR username@tnsname/password CONTROL=load_test_etl1.ctl, LOG=loadtestetl.log, BAD=load_stud_etl1.bad, DATA=load_stud_etl1.txt,DISCARD=load_stud_etl1.dis,SKIP=1

double click the batch file to execute.

using SQL* Plus check whether data is loaded with

select * from TEST_ETL1;

Now we would like to use the filter criteria in the load_test_etl1.ctl file as file.

check for filter is applied with when (ename = 'avijit')

LOAD DATA
INFILE 'load_stud_etl5.txt'
BADFILE 'load_stud_etl3.bad'
DISCARDFILE 'load_stud_etl3.dsc'
INSERT INTO TABLE "TEST_ETL5"
when (ename = 'avijit')
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(no, eNAME OPTIONALLY ENCLOSED BY '"', mark OPTIONALLY ENCLOSED BY '"' )

run the batch file load_stud.bat bat to see the results.

check the discard file load_stud_etl3.dsc. Data not matching the criteria are filtered to this file.

Check for the expert section

Check for filtering data into multiple tables