Re: Sql*ldr and float dataformat
Date: 15 Aug 2006 23:42:18 -0700
Message-ID: <1155710538.879758.7600_at_m79g2000cwm.googlegroups.com>
DA Morgan skrev:
> 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?
>
> 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.
>
> 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
hI,
Good suggestion about the translate function.
I did'nt think of it... It seems to work perfectly.
But this is a hack. Anybody who has a clue on the "text-book"-solution?
The language setting, where do we correct that. is that on the session? Or is it possible to configure this in the ctl-file.
kri... :D
And Frank... Sorry.. Forgot about the version etc. Is this suffice?
Windows XP
[Quoted] [Quoted] Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production [Quoted] PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production NLSRTL Version 9.2.0.5.0 - Production Received on Wed Aug 16 2006 - 08:42:18 CEST