We help IT Professionals succeed at work.

SQLLDR question - ORA-01722: invalid number error-Self solved

wasser asked
I've solved this myself, it is an odd solution but it works.  I'm leaving this question here in the hopes it will save someone else from spending as much time on this problem as I spent.
If anyone knows how to get this to change from a question waiting for an answer to just something in the archives, please let me know.
wasser   Oct 4, 2001

I'm not a regular user of SQLLDR (SQL loader)
but I'm having the problem detailed below.  I've checked everything I can think of,
but am puzzled by the error I'm receiving.  The numbers look like valid numbers to me.
Can anyone help?

Thanks in advance.

the *.ctl file:

INFILE 'lod1998col.dat'
BADFILE 'lod1998col.bad'
DISCARDFILE 'lod1998col.dsc'
   fields terminated by ',' optionally enclosed by '"'

a sample of my data

command line:
sqlldr userid=userid control=filename.ctl log=filename.log errors=999999999

The log file (condensed)

SQL*Loader: Release - Production on Fri Sep 21 19:45:28 2001

(c) Copyright 1999 Oracle Corporation.  All rights reserved.

Control File:   lod1998col.ctl
Data File:      lod1998col.dat
  Bad File:     lod1998col.bad
  Discard File: lod1998col.dsc
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 999999999
Bind array:     64 rows, maximum of 65536 bytes
Continuation:    none specified
Path used:      Conventional

Table SEED_COLL, loaded from every logical record.
Insert option in effect for this table: APPEND

 Column Name Position Len  Term Encl Datatype
------------ -------- ---- ---- ---- -----------------
SEED_COLL_SEQ                       SEQUENCE (MAX, 1)
SEED_ID         FIRST   *   ,  O(") CHARACTER            
SEED_CD          NEXT   *   ,  O(") CHARACTER            
CERT_NR          NEXT   *   ,  O(") CHARACTER            
SEED_NR          NEXT   *   ,  O(") CHARACTER            
SEED_F           NEXT   *   ,  O(") CHARACTER            
SEED_WM          NEXT   *   ,  O(") CHARACTER            
SEED_WS          NEXT   *   ,  O(") CHARACTER            

Record 1: Rejected - Error on table SEED_COLL, column SEED_WS.
ORA-01722: invalid number

Record 2: Rejected - Error on table SEED_COLL, column SEED_WS.
ORA-01722: invalid number

... Every single record is listed here...

Record 304: Rejected - Error on table SEED_COLL, column SEED_WS.
ORA-01722: invalid number

Record 305: Rejected - Error on table SEED_COLL, column SEED_WS.
ORA-01722: invalid number

  0 Rows successfully loaded.
  305 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Space allocated for bind array:                  64015 bytes(35 rows)
Space allocated for memory besides bind array:        0 bytes

Total logical records skipped:          0
Total logical records read:           305
Total logical records rejected:       305
Total logical records discarded:        0

Run began on Fri Sep 21 19:45:28 2001
Run ended on Fri Sep 21 19:46:39 2001

Elapsed time was:     00:01:10.79
CPU time was:         00:00:00.59    

ORACLE table structure

SQL> desc seed_coll;
 Name                   Null?    Type
 ---------------------- -------- ----------------------------
 SEED_ID                NOT NULL VARCHAR2(8)
 SEED_CD                NOT NULL VARCHAR2(4)
 CERT_NR                NOT NULL VARCHAR2(6)
 SEED_NR                         NUMBER(5)
 SEED_F                          NUMBER(3)
 SEED_WM                         NUMBER(7,2)
 SEED_WS                         NUMBER(7,2)
 COLL_REMARKS                    VARCHAR2(70)

Access table structure:
Name                   Type
 --------------------- ----------------------------
 SEED_ID               Text(8)
 SEED_CD               Text(4)
 CERT_NR               Text(6)
 SEED_NR               Number Long Int(0 decimal places)
 SEED_F                Number Int(0 decimal places)
 SEED_WM               NUMBER Double (2 decimal places)
 SEED_WS               NUMBER Double (2 decimal places)

Watch Question

Some lines have empty fields. I know Sqlldr doesn't like that.
and the other thing might be the decimal '.' , check what you have defined as decimal character.



I checked my decimal - it is defined as HEX 2E, while I was at it I checked commas:HEX 2C, and double quotes are HEX 22 & numbers are HEX 30...39.  This all appears good according to my ASCII conversion chart.

I did finally got the data to load.
It appears that on our system, if the last field being loaded is a number type, then it needs to be terminated by a comma (to terminate the record???).  I've never had to terminate records in this manner before when I use comma delimited files, but this is the first time I've used sqlldr with ORACLE version 8i.  Seems like our system is taking the "  fields terminated by ',' ..." clause very literally!  

Right now I consider my problem solved, I don't understand why it works this way, but work it does.

Thanks jbever for providing some feedback.

wasser   10/2/2001

Hi Wasser,
How did you find that one ...
I had not tried the sqlloader /w O8i.
(only 734)

I'll see if i can reproduce the problem.


Jan (jbever)
I found it almost by mistake.  I decided to try adding all fields existing in the ORACLE table to my Access database, even though I had NO data for them, which meant that I hung a REMARKS field off the tail end of the Access database.  When I exported the Access data to *.csv (comma delimited file), it terminated each record with a comma, BUT DID NOT ENCLOSE THE REMARKS FIELD WITH DOUBLE QUOTES AS I HAD NO DATA IN THAT FIELD.  I forgot to change my ORACLE control file to include the REMARKS field, but the data loaded!
I was really just trying every thing I could think of, and beating my head against the wall, but eventually my persistance paid off.  Who says mistakes aren't valuable?

However, when I sent my files to ORACLE support, they ran it just fine, so perhaps this is just some quirk in our particular system??

I tried here , little test, and no , at end.

Works. Weird.
Just hope oracle isn't going the MS route..


Please update and finalize this old, open question. Please:

1) Award points ... if you need Moderator assistance to split points, comment here with details please or advise us in Community Support with a zero point question and this question link.
2) Ask us to delete it if it has no value to you or others
3) Ask for a refund so that we can move it to our PAQ at zero points if it did not help you but may help others.



** Mindphaser - Community Support Moderator **

P.S.  Click your Member Profile, choose View Question History to go through all your open and locked questions to update them.
Author of the Year 2009
Lacking timely response from wasser or contributing experts, but seeing that there is some useful info here, I recommend:

    Refund points and save as a 0-pt PAQ.

DanRollins -- EE database cleanup volunteer


I'm accepting the moderator's and clean-up volunteer's comment as answer to get this old thing off the list of needing an answer.  

I suggest making a self comment/answer available to close the question.  I never saw this in the options on how to close it...
Author of the Year 2009

Will you please remove that C from my permanent record?  It is already following me around to other places where I do volunteer work.  The Salvation Army say they don't want me to play Santa next Christmas.  "We don't need no low-rated belly laffs from the likes of you."  It's ruining my life.

-- Dan

Better late than never - yes, it appears that when you make a global declaration like FIELDS TERMINATED BY ',' sqlldr really wants to see a comma at the end of each and every field (even though it specifically states in "Oracle SQL*Loader: The Definitive Guide" by O'Reilly that it should be smart enough to move on to the next record).  However, another way you can get around this is by declaring a local TERMINATED BY WHITESPACE on the last field in the record, e.g.:


This works for me on Oracle 9iR2 on Linux.

Oh man, one of those leftovers ... been there myself several times.

refunded points and changed grade to 'A'.

** Mindphaser - Community Support Moderator **

I had a similar problem with sqlldr and oracle 9i. The only difference is that the last feild is CHAR and the error is "Field in data file exceeds maximum length". If I terminate by whitespace then part of the feild is gone because they contain embedded spaces. If I use many spaces the feilds that are completely filled come as errors and using CHAR (40), the actual size, in the control file doesn't work either. I cannot insert a comma by hand because the file is extreemly large and I could write a program to insert a comma but I am thinking there must be an easier way. Any ideas ?


I'm assuming that your database field is set to CHAR(40)
that your data field is no longer than that...
have you checked?

do you have a text editor?  something like Wylbur or Brief?  that would allow you to replace the end-of-line/carrige-return sequence with a comma/end-of-line/carrige-return sequence?

If this doesn't answer your question, you may wish to post as a question to EE rather than a comment tacked onto the end of my original question.
Cheers & good luck,