SQL Loader

SQL*Loader Files
---------------
SQL*Loader uses the following files:

DATA File
---------
A DAT file containing the data to be loaded

Control File
------------
A CTL file containing all the control information

LOG File
--------
A LOG file containing entries describing what SQL*Loader did

BAD File
--------
A BAD file to store records from the input data file that fail basic validation checks,
 such as nonnumeric data in a numeric field

Discard File
-----------
A DSC file to store the discarded records from the input data file, which are records that do not qualify
to be loaded into the database


SQL*Loader Conventional Path
----------------------------

The conventional path loader essentially loads the data by using standard INSERT statements.


SQL*Loader Direct Path
-----------------------
The SQL*Loader direct path (DIRECT option) has significant performance improvements for many data load applications.
 Some of the performance improvements include reduced CPU usage and reduced time to perform a data load.
 Refer to Oracle9i Database Utilities for a complete description of the SQL*Loader direct path.


SQL*Loader Options
------------------

skip – [0] Allows the skipping of the specified number of logical records.

rows – [64] The number of rows to load before a commit is issued (conventional path only).
  For direct path loads, rows are the number of rows to read from the data file before saving the data in the datafiles

errors – [50] The number of errors to allow on the load

direct – [FALSE] Specifies whether or not to use a direct path load or conventional

load – [ALL] The number of logical records to load.

parfile – [Y] The name of the file that contains the parameter options for SQL*Loader

Filler -- Removal of unwanted columns data.


SQl * loader Modes
-----------------
APPEND
INSERT
REPLACE
TRUNCATE

Example 1:
-------
create table emp_loader
(empno number,ename varchar2(10),
deptno number,sal number(7,2))

control file
------------
load data
infile 'D:\data1.txt'
insert into table emp_loader
fields terminated by ','
optionally enclosed by '"'
(empno,ename,deptno,sal)


Execution:
---------
Goto command promt

C:\sqlldr username/password@tnsname control='D:\Control.ctl'


Example 2:
--------

OPTIONS (ERRORS=100, SILENT=(FEEDBACK))
LOAD DATA
INFILE *
INTO TABLE dept
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(deptno, dname, loc)
BEGINDATA
120,RESEARCH,"SARATOGA"
10,"ACCOUNTING",CLOVELAND
11,"ART","BOSTON"
21,"SALES",ROCHESTER
42,"INT'L","SAN FRANCISCO"

C:\sqlldr username/password@tnsname control='D:\Control.ctl'  log='D:\demo01.log' skip =10 rows=100


Loading LOB Data Using SQL*Loader
-------------------------------

CREATE TABLE lob_tab (
  number_content    NUMBER(10),
  varchar2_content  VARCHAR2(100),
  date_content      DATE,
  clob_content      CLOB,
  blob_content      BLOB
);

LOAD DATA
INFILE 'lob_test_data.txt'
   INTO TABLE lob_tab
   FIELDS TERMINATED BY ','
   (number_content    CHAR(10),
    varchar2_content  CHAR(100),
    date_content      DATE "DD-MON-YYYY" ":date_content",
    clob_filename     FILLER CHAR(100),
    clob_content      LOBFILE(clob_filename) TERMINATED BY EOF,
    blob_filename     FILLER CHAR(100),
    blob_content      LOBFILE(blob_filename) TERMINATED BY EOF)


Notice that the filename columns in the datafile are marked as FILLERs, so they are not loaded into the table,
but they are used in the LOBFILE definition to identify the loacation of the LOB information.

The data is then loaded using the following SQL*Loader command, run from the command line in the same directory as files.

sqlldr userid=test/test@db10g control=lob_test.ctl log=lob_test.log bad=lob_test.bad

Ur's

AmarAlam