Hi,
I have data in a text file in the following format,

123=abc,
345=def

My database table has the following fileds,

field1 -> corresponds to tag 123 in input file
field2 -> corresponds to tag 345 in inpt file

Can anybody pls help me in how to write the control field
specification for this case.

Thanx
richard

Richard,

My suggestion for the control file is at the bottom of my post. It is based
upon Chapter 9 on Tom Kyte's book "Expert One-on-One Oracle".

Be aware that my suggestion is not very scalable because there is a 258
character limit on the quoted expression that follow the column name in the
field list.

I would suggest that a more scalable solution would be to load the raw data
into another table and then run either a PL/SQL program against that table
or write an enormous CREATE TABLE x AS SELECT statement using the
expressions I have provided.

Douglas Hawthorne

Proposed Control File
=====================

LOAD DATA
INFILE *
CONTINUEIF LAST = ','
INTO TABLE twocols
INSERT
FIELDS TERMINATED BY ','
(
col123
"CASE
WHEN SUBSTR(:col123,1,INSTR(:col123,'=')-1) = '123' THEN
SUBSTR(:col123,INSTR(:col123,'=')+1)
WHEN SUBSTR(:col456,1,INSTR(:col456,'=')-1) = '123' THEN
SUBSTR(:col456,INSTR(:col456,'=')+1)
ELSE NULL
END",
col456
"CASE
WHEN SUBSTR(:col123,1,INSTR(:col123,'=')-1) = '456' THEN
SUBSTR(:col123,INSTR(:col123,'=')+1)
WHEN SUBSTR(:col456,1,INSTR(:col456,'=')-1) = '456' THEN
SUBSTR(:col456,INSTR(:col456,'=')+1)
ELSE NULL
END"
)
BEGINDATA
123=abc,
456=def
456=ghi,
123=jkl

"richard green" <green_24 (AT) hotmail (DOT) com> wrote