Control file in Oracle

Control File:-

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.
Data types 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---------->    ANY CONCURRENT PROGRAM NAME



Execute that command:-

sqlldr user = user/password@database_name control =    control_file_name.ctl;


Example:-

We create a control file XXC05_CONTROL_EMP.ctl

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


   
Note:- When we execute sqlloder command 3 more file created i.e log file, bad file, discard file with the same name of ctl file name.

    Log File:- In that file we see the log details of control file. File extension is "file_name.log".
   
    Bad File:- Which date have logical error (for example:- datatype mismatch, length, etc) goes in that file. File extension is "file_name.bad".

    Discard File:- Which data are not satisfy in when condition goes in that file.


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


LOad data
infile * 
INSERT/APPEND/DELETE/TRUNCATE
into table XXC05_EMP
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 XXC05_EMPLOYEES
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 XXC05_EMPLOYEES
fields terminated by ','
(emp_id constant 100,last_name)
begindata
HR
HR
AA
BB


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


load data
infile *
insert into table XXC05_EMPLOYEES
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 XXC05_EMPLOYEES
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)-- skip first 2 lines 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 '/u02/oracle/visappl/wip/11.5.0/data/XXC05_DATA_FILE'
TRUNCATE
INTO TABLE XXC05_EMPLOYEES
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 XXC05_EMPLOYEES
(
LAST_NAME  position(1:7)  CHAR "UPPER(:LAST_NAME)",
FIRST_NAME position(8:15) CHAR "LOWER(:FIRST_NAME)"
)

BEGINDATA
Locke Phil
Gorman Tim





Thanks
Sajal Agarwal