OraFAQ Forum: Server Utilities » SQL LOADER ORA-01722

Home » RDBMS Server » Server Utilities » SQL LOADER ORA-01722 (Oracle 10)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL LOADER ORA-01722 [message #631143]

Fri, 09 January 2015 12:47

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!

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631144 is a reply to message #631143]

Fri, 09 January 2015 12:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Oracle is too dumb to lie.
believe the error message.

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631147 is a reply to message #631144]

Fri, 09 January 2015 13:15

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...

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631149 is a reply to message #631147]

Fri, 09 January 2015 13:57

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Please, post SQL*Plus output of the

desc tu_categoria_cita

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631153 is a reply to message #631143]

Fri, 09 January 2015 14:24

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

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

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?

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631164 is a reply to message #631154]

Fri, 09 January 2015 15:27

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631167 is a reply to message #631149]

Fri, 09 January 2015 15:46

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

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631168 is a reply to message #631153]

Fri, 09 January 2015 15:49

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

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631169 is a reply to message #631154]

Fri, 09 January 2015 15:53

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

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631170 is a reply to message #631164]

Fri, 09 January 2015 15:56

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

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631175 is a reply to message #631170]

Fri, 09 January 2015 17:55

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: SQL LOADER ORA-01722 [message #631176 is a reply to message #631147]

Fri, 09 January 2015 22:18

flyboy
Messages: 1903
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

Barbara Boehmer
Messages: 9104
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.

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:35:16 CST 2025