Re: Implied decimal points in external tables

I haven't used this, but if you have a working sqlldr script the "external_table" option of sqlldr is purported to generate the corresponding external table definitions. Have a look at:

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96652/ch08.htm#1006964

Also, what you have in your file is a string and not a number (no number has leading zeros). Have you tried using:

  to_number(ltrim('0000001234567','0'))/100

I hope this gets you nearer to what you need.

Cheers,

pbewig_at_swbell.net (Phil Bewig) wrote in message news:<455f7154.0409011215.4dcb847e_at_posting.google.com>...
> I want to load data via an external table. One field
> in the table is defined
>
> trans_amt number(13,2)
>
> The external table is fixed width and the amount is
> stored filled with leading zeros and an implied
> two-digits behind the decimal point. For instance,
> the number 12,345.67 is stored in the
> external table as
>
> 0000001234567
>
> With SQL*Loader I can define the field as
>
> trans_amt position(135:147) ":trans_amt/100"
>
> but that doesn't work with external tables. I've played
> with decimal, zoned, and external but don't yet know how
> to properly read that number.
>
> Can anyone help?
>
> Many thanks,
>
> Phil
Received on Thu Sep 02 2004 - 00:51:20 CDT