Re: SQL*Loader problem

Brad Puett wrote:
> =
 

> In article <323DB19D.3BBD_at_ny.ericsson.se>, Jan Ekl=F6f
> <ERAJEKF_at_ny.ericsson.se> wrote:
> > Having trouble loading data into an oracle 7.1 db with SQLLOAD.
> >
> > The datafile to be loaded have a field with a numerical value which
> > has 1 decimal (definition in oracle table: NUMBER(4, 1) ).
> >
> > In the datafile, the value is written in character form:
> > 0110
> > which is the value 11.0.
> >
> > How can I write the controlfile for SQLLOAD to make SQLLOAD
> > understand that the last character in this field is a decimal ?
> =
 

> I would be real interested if someone else has a better answer ...
> =
 

> What I did was try to keep it simple:
> =
 

> * I left the value in character form and loaded it as is ...
> =
 

> * Then I went into SQL*Plus and did an "Update" on the table I had ju=
 st
> loaded by dividing that field by 10, effectively shifting the=
 

> decimal
> point over 1 place ... Since you can do a general update (i.=
 e.
> without
> a Where Clause)
> =
 

> Update TABLE_A Set FIELD_A =3D FIELD_A / 10;
> =
 

> This is a fairly easy solution ... If you already had data =
 in
> the table,
> you would just need to add a WHERE clause to the above UPDAT=
 E that
> "narrowed" the Update to the few lines loaded (ex. Includin=
 g
> a "Modified
> TimeStamp" field) ...
> =
 

> Hope this helps ... If anyone has a "cleaner" solution (ex. less steps),=
 

> plese let
> us know ...

I had posted the following solution earlier. Plz repost if the solution =

works. =

In the sqlldr control file specify the foll for the field in question. =

field position (1:4) decimal external ":field/10" =

the double quotes are required.

u may refer the Oracle utilities guide for additional info on using =

SQL functions on data while using SQlloader. -- =

Regards,

Varad Acharya Received on Wed Sep 18 1996 - 00:00:00 CEST