Re: SQLLoader : How to specify the field specs

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.com> wrote in message news:811617d5.0402180805.1201ce5b_at_posting.google.com...
> 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
Received on Thu Feb 19 2004 - 00:48:48 CET