SQL*Loader

SQL*Loader:

SQL Loader is an Oracle database utility, used for data loading from flat-files into the database table.

Files used in SQL* Loder are Data_file, Control file, Bad_file, Doscard_file, Log_file.
    Data Source: It can be a flat file(data_file) / stdin(standard input) -> file redirect in command prompt while executing sqlldr.
    Control file: It gives the mechanism to load, from which file, to which table/s, separator, etc.
    Bad file: File that receives data_file records that cannot be loaded due to errors.
    Discard file: File that receives input data records that are not loaded into any table because none of the selection criteria are met for tables with the WHEN clause

Data Loading Path
 Conventional Path (DIRECT = FALSE) default, Loading is slow
 Direct Path (DIRECT = TRUE) Loading is Fast
      It will write the data directly into free/fresh data-block after “High Water Mark” in data files.
      It won’t check free block before “High Water Mark”.

Commandline Options:
BINDSIZE = n
COLUMNARRAYROWS = n
DIRECT = {TRUE | FALSE}
ERRORS = n
LOAD = n
MULTITHREADING = {TRUE | FALSE}
PARALLEL = {TRUE | FALSE}
READSIZE = n
RESUMABLE = {TRUE | FALSE}
RESUMABLE_NAME = ‘text string’
RESUMABLE_TIMEOUT = n
ROWS = n
SILENT = {HEADER | FEEDBACK | ERRORS | DISCARDS | PARTITIONS | ALL}
SKIP = n
SKIP_INDEX_MAINTENANCE = {TRUE | FALSE}
SKIP_UNUSABLE_INDEXES = {TRUE | FALSE}
STREAMSIZE = n

executing SQL* Loader from command prompt:
SQLLDR CONTROL=’/control/file/path/ctrl.ctl’ USERID=userid/password PORT=’1234′ log=’logfile_name.log’ DISCARD=’discard_file.disc’ BINDSIZE=2 DIRECT=TRUE ERRORS=100 LOAD=1000 PARALLEL=TRUE SKIP=2

For data source STDIN:
SQLLDR CONTROL=’/control/file/path/ctrl.ctl’ USERID=userid/password PORT=’1234′ log=’logfile_name.log’ DISCARD=’discard_file.disc’ < ‘/data/file/path/dat_file.dat’

Control file:
LOAD DATA
INFILE ’emp.dat’
BADFILE ’emp.bad’
APPEND INTO TABLE emp
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
( empno, ename )

Positional data
LOAD DATA
INFILE ’emp_fixed.dat’
BADFILE ’emp_fixed.bad’
APPEND INTO TABLE emp
TRAILING NULLCOLS
( empno POSITION (1:4),
ename POSITION (5:14) )

Filler/Boundfiller
LOAD DATA
INFILE ’emp.dat’
BADFILE ’emp.bad’
APPEND INTO TABLE empjob
FIELDS TERMINATED BY ‘,’
OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
( empno,
ename,
job BOUNDFILLER,
mgr BOUNDFILLER,
hiredate FILLER,
jobdesc “:job || ‘ for manager ‘ || :mgr” )

Positional data / select statement / NULLIF
LOAD DATA
INFILE ’emp_fixed.dat’
BADFILE ’emp_fixed.bad’
APPEND INTO TABLE emp
TRAILING NULLCOLS
( empno POSITION (1:4),
ename POSITION (5:14),
job POSITION (15:23) “(SELECT dname FROM dept WHERE deptno = :deptno)”,
mgr POSITION (24:27),
hiredate POSITION (28:38),
sal POSITION (39:46) NULLIF job = ‘MANAGER’,,
deptno POSITION (47:48),
comm POSITION (49:56) NULLIF job <> ‘SALESMAN’ AND deptno <> ’30’)

Data in controlfile itself
LOAD DATA
INFILE *
INTO TABLE modified_data
(  rec_no                      “my_db_sequence.nextval”,
  region                      CONSTANT ’31’,
  time_loaded                 “to_char(SYSDATE, ‘HH24:MI’)”,
  data1        POSITION(1:5)  “:data1/100”,
  data2        POSITION(6:15) “upper(:data2)”,
  data3        POSITION(16:22)”to_date(:data3, ‘YYMMDD’)”
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112

Loading from multiple input files
LOAD DATA
INFILE file1.dat
INFILE file2.dat
INFILE file3.dat
APPEND
INTO TABLE emp
( empno  POSITION(1:4)   INTEGER EXTERNAL,
 ename  POSITION(6:15)  CHAR,
 deptno POSITION(17:18) CHAR,
 mgr    POSITION(20:23) INTEGER EXTERNAL
)

Loading into multiple tables
LOAD DATA
INFILE *
INTO TABLE tab1 WHEN tab = ‘tab1’
( tab  FILLER CHAR(4),
  col1 INTEGER
)
INTO TABLE tab2 WHEN tab = ‘tab2’
( tab  FILLER POSITION(1:4),
  col1 INTEGER
)
BEGINDATA
tab1|1
tab1|2
tab2|2
tab3|3

Load only the selective records that one needs.
LOAD DATA
INFILE  ‘mydata.dat’ BADFILE  ‘mydata.bad’ DISCARDFILE ‘mydata.dis’
APPEND
INTO TABLE my_selective_table
WHEN (01) <> ‘H’ and (01) <> ‘T’ and (30:37) = ‘20031217’
(
  region              CONSTANT ’31’,
  service_key         POSITION(01:11)   INTEGER EXTERNAL,
  call_b_no           POSITION(12:29)   CHAR
)

How does one load records with multi-line fields?
load data
infile “test.dat” “str ‘|n'”
into test_table
fields terminated by ‘;’ TRAILING NULLCOLS
(
desc,
txt
)

— test.dat:
one line;hello dear world;|
two lines;Dear world,
hello!;|

Leave a Comment

Your email address will not be published. Required fields are marked *

Twitter
YouTube
Pinterest
LinkedIn