SQL Loader Commands

SQL LOADER 

SYNTAX

OPTIONS(SKIP=1)
LOAD DATA
infile '*'  --file path
REPLACE/INSERT/APPEND ---optional
into table table_name
fields terminated by ','
optionally enclosed by '"'
trailing nullcols  ---skips if null columns
(
Column1,
Column2,
Column3,
Column n
)


OPTION statement precedes the LOAD DATA statement. The OPTIONS parameter allows you to specify runtime arguments in the control file,
rather than on the command line. The following arguments can be specified using the OPTIONS parameter.
SKIP = n — Number of logical records to skip (Default 0)
LOAD = n — Number of logical records to load (Default all)
ERRORS = n — Number of errors to allow (Default 50)
ROWS = n   — Number of rows in conventional path bind array or between direct path data saves (Default: Conventional Path 64, Direct path all)
BINDSIZE = n — Size of conventional path bind array in bytes (System-dependent default)
SILENT = {FEEDBACK | ERRORS | DISCARDS | ALL} — Suppress messages during run
(header, feedback, errors, discards, partitions, all)
DIRECT = {TRUE | FALSE} –Use direct path (Default FALSE)
PARALLEL = {TRUE | FALSE} — Perform parallel load (Default FALSE)
You can skip columns using the ‘FILLER’ option.

LOADDATA statement is required at the beginning of the control file.
INFILE: INFILE keyword is used to specify location of the datafile or datafiles.
INFILE* specifies that the data is found in the control file and not in an external file. INFILE ‘$FILE’, can be used to
send the filepath and filename as a parameter when registered as a concurrent program.
INFILE   ‘/home/vision/kap/import2.csv’ specifies the filepath and the filename.

Default path for Discard, bad and log files
If bad and discard file paths are not specified in the CTL file and if this SQL Loader is registered as a concurrent program, then they will be created in the directory where the regular Concurrent programs’ output files reside.
You can also find the paths where the discard and bad files have been created in the log file of the SQL LOADER concurrent request.

Command to Execute

sqlldr apps/apps_password@host:port/sid control=control_file_name.ctl log=log_file_name.log


sqlldr USERID=scott/tiger CONTROL=<control filename> LOG=<Log file name>

INIT.ORA file contains the default values for the commit point


Register .ctl as concurrent Program:

Place the Control file in $CUSTOM_TOP/bin.

Define the Executable. Give the Execution Method as SQL*LOADER.

Define the Program. Add the Parameter for FILENAME.


Can one selectively load only the records that one need?

Look at this example, (01) is the first character, (30:37) are characters 30 to 37:
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) = '19991217'
( region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR )

What is the difference between the conventional and direct path loader?

The conventional path loader essentially loads the data by using standard INSERT statements.
The direct path loader (DIRECT=TRUE) bypasses much of the logic involved with that, and loads directly into the Oracle data files.