Re: Problems with SQL*LOADER and numbers

From: Barth Siemens <nospam_at_uniserve.com>
Date: Tue, 20 Jul 1999 09:02:14 GMT
Message-ID: <379437a7.934142125_at_news.uniserve.com>


On Mon, 19 Jul 1999 13:48:44 +0200, "Jérôme Poirier" <poirier_at_gla.ecoledoc.lip6.fr> wrote:

>I use SQL*LOADER to fill in the following table (the name of which is
>"jerome") :
> Nom Non renseigné NULL? Type
> ------------------------------- -------- ----
> ISO_NUM NUMBER(3)
> (...)
> DERNIER_COURS_VENTE_CONNU NUMBER(3,9)
>
>The data file contains :
> "504","MAD","204","DIRHAM MAROCAIN","0","3.1"
> "280","DEM","4","DEUTSCHE MARK RF","0","3.299864677"
> (...)
>
>The control file looks like :
> LOAD DATA
> INFILE 'C:\TEMP\COURS\COURS.ASC'
> REPLACE
> INTO TABLE VL.JEROME
> FIELDS TERMINATED ',' OPTIONALLY ENCLOSED '"'
> (
> ISO_NUM,
> (...)
> DERNIER_COURS_VENTE_CONNU
> )
>
>When I launch SQL*LOADER, it answers (log file) :
> Enregistrement 1 : Rejeté - Erreur sur table EMP, colonne SAL.
> ORA-01722: Nombre invalide
>
> Enregistrement 2 : Rejeté - Erreur sur table EMP, colonne SAL.
> ORA-01722: Nombre invalide
>
> (...)
>
>(in english : "Record 1 : Rejected - Error into table EMP, row SAL.
>ORA-01722: Invalid number")
>
>SQL*LOADER seems not to understand floating numbers like 3.299864677. I have
>tryed 3,299864677 ("," or "." as separator) but it don't recognize the
>number.

Because the table name and column name don't match what you originally showed to us, it really looks to me like SQL*LOADER is somehow pointing at the wrong control file.

If, on the other hand, you've just changed the names of the table & column, here are my suggestions for DERNIER_COURS_VENTE_CONNU.

  • DDL STATEMENT --
You're precision (3) is less than your scale (9), and you wouldn't be able to insert those records via SQL*PLUS either. You need to ALTER TABLE to modify the precision from 3 up to about 12, so you can have 3 digits to the left of the decimal and 9 to the right of the decimal.
  • SQL*LOADER STATEMENT --
PS. It may also be helpful to define DERNIER_COURS_VENTE_CONNU as DECIMAL EXTERNAL in the control file.
--
Barth Siemens 
Change the email address from nospam to bsiemens.
Received on Tue Jul 20 1999 - 11:02:14 CEST

Original text of this message