Re: Sql*ldr and float dataformat

ranne wrote:
> Hi,
>
> I have some problem with loading data into tables.
> Float datatype.
>
> an example ctl file:
> **********************************************
> LOAD DATA
>
> INTO TABLE <table_name>
>
> TRUNCATE
>
> FIELDS TERMINATED BY X'09'
>
> TRAILING NULLCOLS
>
> (
>
> ANNUAL_RENT "to_number(trim(:ANNUAL_RENT),
> '9999999999999999999.9999')",
> ADJUSTMENT FLOAT EXTERNAL,
>
> PREMISES_TYPE_VL INTEGER EXTERNAL,
>
> PREMISES_TYPE_RD INTEGER EXTERNAL,
>
> TENANT_TYPE_VL INTEGER EXTERNAL,
>
> TENANT_TYPE_RD INTEGER EXTERNAL,
>
> PRICE "to_number(trim(:PRICE),
> '9999999999999999999.9999')",
>
> )
> ****************************
> The "float external" does not work. the problem is that my language
> settings is looking for ' , ' instead of ' . ' in the float section.
> The float-number = 12.00012 is not recognized but if it say 12,00012
> the loader would load the data.
>
> I could use a to_number as i have with price-column, (that column has
> datatype number(19,4) therefore the format). But Im not sure what
> format would be valid for float data.
>
> I belive that either a language setting is in order (preferably set on
> the ctl-file) or to_number with correct format. Any clues?

[Quoted] There is likely a better solution than the one I am going to suggest but you can use the TRANSLATE function in your control file to replace periods with commas.

[Quoted] See the demos of SQL*Loader in Morgan's Library at www.psoug.org. There is a demo using UPPER that shows the syntax.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org