Preview a little ’bout sql*loader
SQL*Loader loads data from external files into tables of an Oracle database,
SQL*Loader Can manipulate the data before loading it, using SQL functions,
SQL*Loader Can load data into multiple tables during the same load session, and many more..

SQL*Loader takes as input a control file (*.ctl), which controls the behavior of SQL*Loader, and one or more datafiles. Output of the SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially a discard file.

Requirement..
1. Table
2. Control File
3. Flat File / Data File (optional)


Example

1. —Create User
create user loader
identified by loader
default tablespace loaders
temporary tablespace temp

—Grant To Tablespace
GRANT UNLIMITED TABLESPACE TO loader

—Making A Table
CREAT E TABLE loader.load_here
(
col1 VARCH4R2(10),
col2 VARCH4R2(10),
col3 VARCH4R2(10)
)

2. vi loader.ctl
LOAD DATA
INFILE '/tmp/flat_file.csv'
REPLACE
INTO TABLE loader.load_here
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(col1,col2,col3)

The INFILE says, “where file was load”
The REPLACE keyword says, “remove any existing rows before starting the load.”
INTO TABLE, “yes, the table where data was insert”
The TRAILING NULLCOLS statement handles the missing data; it tells SQL*Loader to load any missing data as NULL values
FIELDS TERMINATED BY ‘,’ says the delimiter from csv file, default is ” , ”

3. cat flat_file.csv
a,nama1,staff
b,nama2,manager
c,nama3,direktur

Run SQL*Loader
$ cd /tmp
$ ls
loader.ctl
flat_file.csv
$ sqlldr userid=loader/loader control=/tmp/loader.ctl log=/tmp/loader.log

Popularity: 18% [?]

You Should Also Check Out This Post:

More Active Posts: