Re: Can this be done with SQLLoader????

The integer fields should be straigthforward. The date fields could be handled by to_date functions. For a numeric(7,2) where the data is 8 positions long you could use for example (if . is your decimal sign): etc,
xx POSITION(0033:0033),
price POSITION(034:041)
"to_number(substr(:price,1,6)||'.'||substr(:pris,7,2))", xy POSITION(0042:0042),
etc,
You can put a lot sql code inside the " " behind the field, e.g. a "DECODE(...)" function and so on.
Good luck

Scott Mattes skrev i meldingen <36DC2D27.FFE300B6_at_erols.com>...
>I have some flat ascii files that need to be loaded into Oracle. The
>data is a mixture of character, integer, decimal and date (julian dates
>with lengths of 3, 4, 5, 6 and 7 positions). All of the number fields
>are non-binary, in other words, if I open the data file with notepad and
>look at an integer field I see things like '00124' and '10342'. Decimal
>fields that I want in the table as '9999.99' I see in notepad as
>'999999'. The date fields are the same way, in notepad I see things like
>'98100' and '100'.
>
>Can I set up the control file to load these fields into the desired
>table with column types of DATE, NUMERIC(7) and NUMERIC(9,2)? If so,
>how? I have tried adding things like TO_NUMERIC(fieldname) to the
>control file statements and it doesn't like it. OR, do I have to load
>into a temp table and then do a a seperate sql statement to create the
>real table with data conversion functions wrapped around the columns
>that need it?
>
>--
>---------------------------------
>Scott Mattes
>ICQ: 18330579
>Work: Scott.Mattes_at_Wang.com
>Home: SMattes_at_Erols.com
>Web: www.erols.com/smattes
Received on Tue Mar 02 1999 - 23:24:30 CET