Re: Q:SQL Loader and filler cols

Ralf Bender wrote:
> Hi
>
> I'm using Oracle 8.1.7 on Linux. I want to load a ASCII Datafile with
> SQL*Loader. The ASCII File Columns with fixed length and all in Double
> Quotationsmarks. Looks very simpel,mmmh. There 3 columns in the file
> that must be load as 1 to an oracle column and that's my problem.
> so i define fil1 to fil3 as a filler field and concat this 3 to col2.
> But this won't run. I always get oracle errors like ORA-010080.
> Could someone tell me what I'm doing wrong.
>
> here is my control file:
>
> LOAD DATA
> INFILE '/data/demo.dat'
> replace
> into table demo
> (
> col1 position (2:8) "trim(:co1)",
> fil1 filler position(12:46),
> fil2 filler position(50:84),
> fil3 filler position(88:122),
> col2 "trim(:fil1)||','||trim(:fil2))||','||trim(:fil3))",
> col3 position (126:132) "trim(:col3)",
> col4 position(136:142)"trim(:col4)",
> col5 position(146:152) "trim(:col5)"
> )
>
> thanks ralf

Two possible solutions.

  First solution is load the file into a staging table and pull it apart inside the DB with selects and inserts. (this is a brute force all else fails method)

  Second solution is to substring the single field. (you were nearly there but you are going about it the wrong way). That is,

1. get rid of the dummy columns fil1,fil2,fil3
2. change col2 to be position 12:122
3. add the sql expression to get the data you need for col2: "trim(substr(:col2,1,35)) || trim(substr(:col2,49,35) || trim(substr(:col2,77,45)"

Double check my postion and size calculations.

Good luck.

-- 
Ed Prochak
running: http://www.faqs.org/faqs/running-faq/
family:  http://web.magicinterface.com/~collins
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost