Structure of a Control file:
Sample CTL file for loading a Variable record data file:
OPTIONS (SKIP = 1,, ERRORS=99, ROWS=100) –The first row in the data file is skipped without loading and With Will Allow fro 99 errors while loadig and
Rows=100 means , For Every 100 Rows auto commit will happens.
LOAD DATA
INFILE ‘$FILE’ — Specify the data file path and name
APPEND – type of loading (INSERT, APPEND, REPLACE, TRUNCATE
INTO TABLE “APPS”.”BUDGET” – the table to be loaded into
FIELDS TERMINATED BY ‘|’ – Specify the delimiter if variable format datafile
OPTIONALLY ENCLOSED BY ‘”‘ –the values of the data fields may be enclosed in “
TRAILING NULLCOLS – columns that are not present in the record treated as null
(ITEM_NUMBER “TRIM(:ITEM_NUMBER)”, – Can use all SQL functions on columns
QTY DECIMAL EXTERNAL,
REVENUE DECIMAL EXTERNAL,
EXT_COST DECIMAL EXTERNAL TERMINATED BY WHITESPACE “(TRIM(:EXT_COST))” ,
MONTH “to_char(LAST_DAY(ADD_MONTHS(SYSDATE,-1)),’DD-MON-YY’)” ,
DIVISION_CODE CONSTANT “AUD” – Can specify constant value instead of Getting value from datafile
)
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)
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.
Example where datafile is an external file:
LOAD DATA
INFILE ‘/home/vision/kap/import2.csv’
INTO TABLE kap_emp
FIELDS TERMINATED BY “,”
( emp_num, emp_name, department_num, department_name )
Example where datafile is in the Control file:
LOAD DATA
INFILE *
INTO TABLE kap_emp
FIELDS TERMINATED BY “,”
( emp_num, emp_name, department_num, department_name )
BEGINDATA
7369,SMITH,7902,Accounting
7499,ALLEN,7698,Sales
7521,WARD,7698,Accounting
7566,JONES,7839,Sales
7654,MARTIN,7698,Accounting
Example where file name and path is sent as a parameter when registered as a concurrent program
"rtrim(ltrim(replace(:CUSTOMER_NUMBER,chr(13))))"
LOAD DATA
INFILE ‘$FILE’
INTO TABLE kap_emp
FIELDS TERMINATED BY “,”
( emp_num, emp_name, department_num, department_name )
TYPE OF LOADING:
INSERT — If the table you are loading is empty, INSERT can be used.
APPEND
— If data already exists in the table, SQL*Loader appends the new rows
to it. If data doesn’t already exist, the new rows are simply loaded.
REPLACE — All rows in the table are deleted and the new data is loaded
TRUNCATE — SQL*Loader uses the SQL TRUNCATE command.
INTOTABLEis
required to identify the table to be loaded into. In the above example
INTO TABLE “APPS”.”BUDGET”, APPS refers to the Schema and BUDGET is the
Table name.
FIELDS TERMINATED BY specifies how the data fields are terminated in the datafile.(If the file is Comma delimited or Pipe delimited etc)
OPTIONALLY ENCLOSED BY ‘”‘ specifies that data fields may also be enclosed by quotation marks.
TRAILINGNULLCOLS clause tells SQL*Loader to treat any relatively positioned columns that are not present in the record as null columns.
Loading a fixed format data file:
LOAD DATA
INFILE ‘sample.dat’
INTO TABLE emp
( empno POSITION(01:04) INTEGER EXTERNAL,
ename POSITION(06:15) CHAR,
job POSITION(17:25) CHAR,
mgr POSITION(27:30) INTEGER EXTERNAL,
sal POSITION(32:39) DECIMAL EXTERNAL,
comm POSITION(41:48) DECIMAL EXTERNAL,
deptno POSITION(50:51) INTEGER EXTERNAL)
