Sys-Admin Information's

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

CREATE TABLE loader.load_here
(
col1 VARCH4R2(10),
col2 VARCH4R2(10),
col3 VARCH4R2(10)
)

2. Add "ctl" file

bash> 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. Insert the table with csv file

a,nama1,staff
b,nama2,manager
c,nama3,direktur

Run SQL*Loader

bash> cd /tmp

bash> ls
loader.ctl
flat_file.csv

bash> sqlldr userid=loader/loader control=/tmp/loader.ctl log=/tmp/loader.log