Re: SQL Loader

thomas.wolfram_at_gmx.de wrote:

> I want to read a data-file (text) with SQL-Loader into a table. It
> works fine with the exception for numerical data.
> The datafield in the oracle table is defined as NUMBER(8,3) but in the
> textfile I have no decimal point, only 8 characters.
> e.g. Textfile -> 00012000 should become 12,0 in the database.
>
> In the control file for loading, I have specified to Position
> menge_soll POSITION(251:258) xxxxxxxxx
>
> What have I to use for the xxxxx. If I use DECIMAL the error message
> will uncorrect number of bytes. The term EXTERNAL has no effect due to
> the missing decimal point.
>
> Any help is welcome.
> Thanks
>
> Thomas

I may be misunderstandign what you are trying to do but if you are trying to put the string "00012000" into a NUMBER(8,3) as 12.000 (US notation) one solution would be to perform a division during loading by using a function in your load.

Here's an example of modification with a function during loading:

LOAD DATA
INFILE *
INSERT
INTO TABLE decodemo
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(
fld1,
fld2 "DECODE(:fld1, 'hello', 'goodbye', :fld1)" )
BEGINDATA
hello,""
goodbye,""
this is a test,""
hello,""

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)