Home » RDBMS Server » Server Utilities » SQL LOADER ORA-01722 (Oracle 10)
icon5.gif  SQL LOADER ORA-01722 [message #631143] Fri, 09 January 2015 12:47 Go to next message
danielortiz82
Messages: 6
Registered: January 2015
Junior Member
hi,

Im trying to figure out why SQLLDR is sending this log error message:

Record 1: Rejected - Error on table TU_CATEGORIA_CITA, column LIMITE_CITAS.
ORA-01722: Number not valid

My ctl file is:
LOAD DATA
INFILE tu_categoria_cita.txt'
APPEND
INTO TABLE TU_CATEGORIA_CITA
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
CVE_CATEGORIA_CITA,
DESCRIPCION,
LIMITE_CITAS
)

and the txt file is like this:
"1","Inicio",1
"2","Seguimiento",80
"3","Cierre",1

In the DB the table columns datatypes are varchar(2 bytes), varchar(40 bytes) and number(2,0) for the three fields

Thanks in advance!
Re: SQL LOADER ORA-01722 [message #631144 is a reply to message #631143] Fri, 09 January 2015 12:52 Go to previous messageGo to next message
BlackSwan
Messages: 26545
Registered: January 2009
Location: SoCal
Senior Member
Oracle is too dumb to lie.
believe the error message.
Re: SQL LOADER ORA-01722 [message #631147 is a reply to message #631144] Fri, 09 January 2015 13:15 Go to previous messageGo to next message
danielortiz82
Messages: 6
Registered: January 2015
Junior Member
Not sure what you mean...

I believe somehow oracle is receiving a number not valid ... but how?

I just realized that if I put the numeric column in the middle it uploads fine, so it might have to do with the number being in the end of line.

Still would like to know how to fix this issue here because I have some other similar cases.

Any ideas? Do I need to use "terminated by (some kind of end-of-line command)"?

Thanks again...
Re: SQL LOADER ORA-01722 [message #631149 is a reply to message #631147] Fri, 09 January 2015 13:57 Go to previous messageGo to next message
Littlefoot
Messages: 21482
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, post SQL*Plus output of the
desc tu_categoria_cita
icon10.gif  Re: SQL LOADER ORA-01722 [message #631153 is a reply to message #631143] Fri, 09 January 2015 14:24 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Your control file works for me:
SQL*Loader: Release 11.2.0.3.0 - Production on Fri Jan 9 15:21:49 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   test.ctl
Data File:      test.ctl
  Bad File:     test.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 SCOTT.TU_CATEGORIA_CITA, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CVE_CATEGORIA_CITA                  FIRST     *   ,  O(") CHARACTER
DESCRIPCION                          NEXT     *   ,  O(") CHARACTER
LIMITE_CITAS                         NEXT     *   ,  O(") CHARACTER


Table SCOTT.TU_CATEGORIA_CITA:
  3 Rows successfully loaded.
  0 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:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

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


[3eyes]

[Updated on: Fri, 09 January 2015 14:24]

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631154 is a reply to message #631143] Fri, 09 January 2015 14:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66446
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
and the txt file is like this:


What is the min and max values in the file for the third field?

Re: SQL LOADER ORA-01722 [message #631164 is a reply to message #631154] Fri, 09 January 2015 15:27 Go to previous messageGo to next message
BlackSwan
Messages: 26545
Registered: January 2009
Location: SoCal
Senior Member
you don't have to use SQL*Loader.
You could treat the data file as EXTERNAL TABLE & then access it as regular table.
Re: SQL LOADER ORA-01722 [message #631167 is a reply to message #631149] Fri, 09 January 2015 15:46 Go to previous messageGo to next message
danielortiz82
Messages: 6
Registered: January 2015
Junior Member

SQL*Plus: Release 11.2.0.1.0 Production on Vie Ene 9 15:32:55 2015

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

Conectado a:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> desc tu_categoria_cita
Nombre ‚ĒźNulo? Tipo
----------------------------------------- -------- ----------------------------

CVE_CATEGORIA_CITA VARCHAR2(2)
DESCRIPCION VARCHAR2(40)
LIMITE_CITAS NUMBER(2)
FECHA_CARGA DATE



THANKS
Re: SQL LOADER ORA-01722 [message #631168 is a reply to message #631153] Fri, 09 January 2015 15:49 Go to previous messageGo to next message
danielortiz82
Messages: 6
Registered: January 2015
Junior Member
LKBrwn_DBA wrote on Fri, 09 January 2015 14:24
Your control file works for me:
SQL*Loader: Release 11.2.0.3.0 - Production on Fri Jan 9 15:21:49 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:   test.ctl
Data File:      test.ctl
  Bad File:     test.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 SCOTT.TU_CATEGORIA_CITA, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CVE_CATEGORIA_CITA                  FIRST     *   ,  O(") CHARACTER
DESCRIPCION                          NEXT     *   ,  O(") CHARACTER
LIMITE_CITAS                         NEXT     *   ,  O(") CHARACTER


Table SCOTT.TU_CATEGORIA_CITA:
  3 Rows successfully loaded.
  0 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:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

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


[3eyes]


I guess it should work...

the only thing I can think of you might have different is that the table has an extra date field which has a default to SYSDATE, but I dont think that changes anything in the issue

thanks
Re: SQL LOADER ORA-01722 [message #631169 is a reply to message #631154] Fri, 09 January 2015 15:53 Go to previous messageGo to next message
danielortiz82
Messages: 6
Registered: January 2015
Junior Member
Michel Cadot wrote on Fri, 09 January 2015 14:24

Quote:
and the txt file is like this:


What is the min and max values in the file for the third field?



1 to 99
Re: SQL LOADER ORA-01722 [message #631170 is a reply to message #631164] Fri, 09 January 2015 15:56 Go to previous messageGo to next message
danielortiz82
Messages: 6
Registered: January 2015
Junior Member
BlackSwan wrote on Fri, 09 January 2015 15:27
you don't have to use SQL*Loader.
You could treat the data file as EXTERNAL TABLE & then access it as regular table.


Thanks for the option but we need to upload the info into the actual DB (mainly for consistency)...after uploading we will use some stored procedures to work on these tables data
Re: SQL LOADER ORA-01722 [message #631175 is a reply to message #631170] Fri, 09 January 2015 17:55 Go to previous messageGo to next message
BlackSwan
Messages: 26545
Registered: January 2009
Location: SoCal
Senior Member
danielortiz82 wrote on Fri, 09 January 2015 13:56
BlackSwan wrote on Fri, 09 January 2015 15:27
you don't have to use SQL*Loader.
You could treat the data file as EXTERNAL TABLE & then access it as regular table.


Thanks for the option but we need to upload the info into the actual DB (mainly for consistency)...after uploading we will use some stored procedures to work on these tables data


CREATE TABLE REAL_TABLE AS SELECT FROM EXTERNAL_TABLE;

please explain why this is NOT an acceptable method to load data into the DB.
Re: SQL LOADER ORA-01722 [message #631176 is a reply to message #631147] Fri, 09 January 2015 22:18 Go to previous messageGo to next message
flyboy
Messages: 1872
Registered: November 2006
Senior Member
danielortiz82 wrote on Fri, 09 January 2015 20:15
Any ideas? Do I need to use "terminated by (some kind of end-of-line command)"?

Just a wild guess, as your post lacks that information (OS on database system and sqlldr, exact file content), anyway,
I also faced the same error when loading file with DOS line break (CR+LF) to Oracle database on Linux system.
Solution was applying dos2unix utility to the loaded file; its creation having the same line break format as the Oracle database would be fine too.

[Edit: added the last sentence]

[Updated on: Fri, 09 January 2015 22:22]

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631181 is a reply to message #631176] Fri, 09 January 2015 23:19 Go to previous message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
Flyboy is right. This is a common problem when data from one operating system with one line terminator is loaded onto another operating system with a different line terminator. The fact that you only have a problem when the numeric field is last is proof that the terminator is the problem. There are various ways to work around this problem. You can generate the data file differently or convert the data file. You can also trim the extra characters within SQL*Loader or declare the terminator separately for that field.
Previous Topic: How to export using datapump excluding mutiple table in windows
Next Topic: Export of data through Query in oracle standard edition
Goto Forum:
  


Current Time: Mon Jun 24 23:05:59 CDT 2019