Control File with Example

Notes:

The LOAD DATA statement is required at the beginning of the control file.

INFILE * specifies that the data is found in the control file and not in an external file.

The INTO TABLE statement is required to identify the table to be loaded (DEPT) into. By default, SQL*Loader requires the table to be empty before it inserts any records.

FIELDS TERMINATED BY specifies that the data is terminated by commas, but may also be enclosed by quotation marks.
Datatypes for all fields default to CHAR.

Specifies that the names of columns to load are enclosed in parentheses. Since no datatype is specified, the default is a character of length 255.

BEGINDATA specifies the beginning of the data.

put control file in that path———>    /u02/oracle/visappl/wip/11.5.0/bin
Attach those ctl file in concurrent program
Program name———->    XXC07_CTL_FILE_P/ANY NAME OF PROGRAM

sqlldr user=apps/apps@vis control=XXC07_test.ctl

******************************************** 1 ****************************

LOad data
infile *
INSERT/APPEND/DELETE/TRUNCATE
into table XXC04_emp1
when dept_name=’HR’
fields terminated by ‘ ‘
(emp_id position(1:2),last_name position(4:5),salary position(7:9),dept_name position(11:12))
into table XXC04_emp2
when dept_name=’FF’
(emp_id position(1:2),dept_name position(11:12))
begindata
10 AA 200 HR
20 BB 300 HR
30 CC 400 MG
40 DD 500 FF

******************************************** 2 ****************************

Option (skip=1)
load data
infile *
insert into table XXC04_emp2
fields terminated by ‘ ‘
(emp_id,last_name,dept_name FILLER POSITION(1),salary)
begindata
10 HR 200
20 GG 300
30 AA 400

******************************************** 3 ****************************

load data
infile *
insert into table XXC04_emp2
fields terminated by ‘,’
(emp_id constant 100,last_name)
begindata
HR
HR
AA
BB

******************************************** 4 ****************************

load data
infile *
insert into table XXC04_emp2
when (1:1)=’H’
fields terminated by ‘,’
(emp_id constant 100,last_name)
begindata
HR
HR
AA
BB

******************************************** 5 ****************************

load data
infile *
replace into table XXC04_emp2
when (1:1)!=’H’
fields terminated by ‘,’
(emp_id constant 100,last_name,dept_name,rec_no recnum)
begindata
H,A
HR,B
AA,C
BB,D

******************************************** 6 ****************************

options(skip=2)– from skip 2 from the top
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ‘,’
(
image_id   INTEGER(5),
file_name  CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,image1.gif
002,image2.jpg
003,image3.jpg

******************************************** 7 ****************************

LOAD DATA
INFILE *
TRUNCATE
INTO TABLE XXC04_emp2
FIELDS TERMINATED BY “,”  trailing nullcols
(
c1,
field2 BOUNDFILLER,
field3 BOUNDFILLER,
field4 BOUNDFILLER,
field5 BOUNDFILLER,
c2     “:field2 || :field3”,
c3     “:field4 + :field5”
)

******************************************** 8 ****************************

LOAD DATA
INFILE *
INSERT
INTO TABLE XXC04_emp2
(
LAST_NAME  position(1:7)  CHAR “UPPER(:LAST_NAME)”,
FIRST_NAME position(8:15) CHAR “LOWER(:FIRST_NAME)”
)

BEGINDATA
Locke Phil
Gorman Tim

Advertisements