Invalid number when loading a file using sql loader?

Question

Here is what is in my .ctl file:

load data infile './certified.txt' 
append into table certified 
fields terminated by "," (employeeID, airplaneID)

Error(shorted repeating invalid number lines):

SQL*Loader: Release 10.2.0.4.0 - Production on Sat Apr 4 16:41:04 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Control File:   loader.ctl
Data File:      ./certified.txt
  Bad File:     certified.bad
  Discard File:  none specified

 (Allow all discards)

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

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

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPLOYEEID                          FIRST     *   ,       CHARACTER            
AIRPLANEID                           NEXT     *   ,       CHARACTER            

Record 1: Rejected - Error on table CERTIFIED, column AIRPLANEID.
ORA-01722: invalid number

Record 2: Rejected - Error on table CERTIFIED, column AIRPLANEID.
ORA-01722: invalid number
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table CERTIFIED:
  0 Rows successfully loaded.
  51 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:                  33024 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            64
Total logical records rejected:        51
Total logical records discarded:        0

Run began on Sat Apr 04 16:41:04 2015
Run ended on Sat Apr 04 16:41:04 2015

Elapsed time was:     00:00:00.28
CPU time was:         00:00:00.03

The line I used to create the table is:

create table certified (employeeID int, airplaneID int);

And here is what is in the data file (again some data omitted and no spaces between lines, just put that there because I have issues with formatting of this site):

567354612,1
567354612,2
567354612,10
567354612,11
567354612,12

Could it be that those white lines you left out in this question cause the problem? — GolezTrol
– GolezTrol, Commented Apr 4, 2015 at 21:52
i just did that because I cant figure out stack overflows formatting. there are no white lines in the file. — Dean
– Dean, Commented Apr 4, 2015 at 23:46

Dean · Accepted Answer · 2015-04-05 09:41:37Z

2

My problem was that there was an issue with the text files. The following command fixed them up and everything worked perfectly!

perl -p -i -e 's/\r\n$/\n/g' aircraft.txt certified.txt flights.txt employees.txt

answered Apr 5, 2015 at 9:41

Dean

371 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Invalid number when loading a file using sql loader?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related