Home » RDBMS Server » Server Utilities » problem using sqlldr(2 Merged)
problem using sqlldr(2 Merged) [message #520287] Sat, 20 August 2011 10:08 Go to next message
p40l3tt0
Messages: 9
Registered: August 2011
Junior Member
Good Morning,
i have the problem with sqlldr.

This is the table:
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER(9)
B NUMBER(9)
C NUMBER(9)

This is the ctl file:
LOAD DATA
INFILE './PROVA.txt'
REPLACE
INTO TABLE prova
FIELDS TERMINATED BY "\t"
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
A,
B,
C
)

This is the log file:

Record 1: Rejected - Error on table PROVA, column A.
ORA-01722: numero non valido

Record 2: Rejected - Error on table PROVA, column A.
ORA-01722: numero non valido

Record 3: Rejected - Error on table PROVA, column A.
ORA-01722: numero non valido



Help me!!! I'm crazy for error ORA-01722

Thank you very much!!
Bye bye


Re: problem using sqlldr [message #520290 is a reply to message #520287] Sat, 20 August 2011 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
OK you have an error.
Based only upon what you posted, how can we help you now?

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

we don't know what you did.
we don't know what you have.
we can't do anything but scratch our heads looking at the lack of details not provided by you.
Re: problem using sqlldr(2 Merged) [message #520291 is a reply to message #520287] Sat, 20 August 2011 10:34 Go to previous messageGo to next message
p40l3tt0
Messages: 9
Registered: August 2011
Junior Member
Ok!
The version of Oracle is 11g r2

The command is: sqlldr SYSTEM@orcl control=control.ctl

and then i received the error ORA-01722

And 'enough or something else is missing?

Thank you very much!!
Re: problem using sqlldr(2 Merged) [message #520292 is a reply to message #520291] Sat, 20 August 2011 10:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Yes, there is something missing: what is the data that you are trying to load? It is, presumably, that which is causing the error. (And please don't forget to read those guidelines)
Re: problem using sqlldr(2 Merged) [message #520295 is a reply to message #520292] Sat, 20 August 2011 10:58 Go to previous messageGo to next message
p40l3tt0
Messages: 9
Registered: August 2011
Junior Member
Thank you for the answrer.
I have read the guidlines.

The data in the file are as follows:
10 20 30
40 50 60
70 80 90

Bye
Re: problem using sqlldr(2 Merged) [message #520296 is a reply to message #520295] Sat, 20 August 2011 11:06 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Consider the field delimiter specified in the control file, and how the fields are in fact delimited in the data file. Do they match?
Re: problem using sqlldr(2 Merged) [message #520297 is a reply to message #520296] Sat, 20 August 2011 11:08 Go to previous messageGo to next message
p40l3tt0
Messages: 9
Registered: August 2011
Junior Member
Yes the delimiter is the tab!
Sad

Bye
Re: problem using sqlldr(2 Merged) [message #520299 is a reply to message #520297] Sat, 20 August 2011 11:23 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I don't think you have tabs in your file, but spaces.
Re: problem using sqlldr(2 Merged) [message #520300 is a reply to message #520299] Sat, 20 August 2011 11:28 Go to previous messageGo to next message
p40l3tt0
Messages: 9
Registered: August 2011
Junior Member
I don't attach the file but i write it.
For sure the file contains the separator tab
Re: problem using sqlldr(2 Merged) [message #520301 is a reply to message #520300] Sat, 20 August 2011 11:30 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>FIELDS TERMINATED BY "\t"

do you get different results by doing as below?

FIELDS TERMINATED BY CHR(9)
Re: problem using sqlldr(2 Merged) [message #520302 is a reply to message #520301] Sat, 20 August 2011 11:36 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
try TERMINATED BY WHITESPACE as well.
Re: problem using sqlldr(2 Merged) [message #520305 is a reply to message #520302] Sat, 20 August 2011 13:18 Go to previous messageGo to next message
p40l3tt0
Messages: 9
Registered: August 2011
Junior Member
Thank you very much John Watson!! It's fantastic Smile

Another error whith a big table on varchar column.

ORA-12899 value too large for column.

You have a resolution for this?
If I was too generic, I can provide you with details like the previous problem

Thank you very much
Re: problem using sqlldr(2 Merged) [message #520306 is a reply to message #520305] Sat, 20 August 2011 13:23 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I think we can conclude that your data file does not have tabs.
And I think you have had enough guidance from me and BS on problem solving technique that you should be able to fix this next problem yourself.
Re: problem using sqlldr(2 Merged) [message #520308 is a reply to message #520306] Sat, 20 August 2011 14:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
12899, 00000, "value too large for column %s (actual: %s, maximum: %s)"
// *Cause: An attempt was made to insert or update a column with a value
//         which is too wide for the width of the destination column.
//         The name of the column is given, along with the actual width
//         of the value, and the maximum allowed width of the column.
//         Note that widths are reported in characters if character length
//         semantics are in effect for the column, otherwise widths are
//         reported in bytes.
// *Action: Examine the SQL statement for correctness.  Check source
//          and destination column data types.
//          Either make the destination column wider, or use a subset
//          of the source column (i.e. use substring).


It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
Re: problem using sqlldr(2 Merged) [message #520312 is a reply to message #520306] Sun, 21 August 2011 01:59 Go to previous messageGo to next message
p40l3tt0
Messages: 9
Registered: August 2011
Junior Member
John Watson wrote on Sat, 20 August 2011 20:23
I think we can conclude that your data file does not have tabs.
And I think you have had enough guidance from me and BS on problem solving technique that you should be able to fix this next problem yourself.


Maybe I'll be stupid. The file is attached. You see spaces or tabs?
  • Attachment: PROVA.csv
    (Size: 0.02KB, Downloaded 1314 times)
Re: problem using sqlldr(2 Merged) [message #520315 is a reply to message #520312] Sun, 21 August 2011 02:52 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I'm not going to call you "stupid", because that would break the rules of the forum. But I am going to quote an Eglish proverb: "You can lead a horse to water, but you can't make it drink".

You have already been given a perfectly worked example of how to investigate this type of problem. You have:

1. An error message
2. A table definition
3. A data file definition
4. A data file

So consider these components. How could a mismatch between 2, 3, and 4 cause 1?
Re: problem using sqlldr(2 Merged) [message #520325 is a reply to message #520315] Sun, 21 August 2011 09:45 Go to previous messageGo to next message
p40l3tt0
Messages: 9
Registered: August 2011
Junior Member
You're right, I apologize, but unfortunately they are very nervous because I can not fix this error!

I will try to formulate the questions correctly.

I can not figure out how to release the code tags when I write a message.

However the table is:

D VARCHAR2(100)
A NUMBER(9)
B NUMBER(9)
C NUMBER(9)

The ctl file is:
LOAD DATA
INFILE './PROVA.txt'
REPLACE
INTO TABLE prova
FIELDS TERMINATED BY WHITESPACE
OPTIONALLY ENCLOSED BY '"'
(
D,
A,
B,
C
)

The datafile is:
SULL'ADDA 1 2 3
PAOLO BIANCO 4 5 6
ALL ALBA 7 8 9


The first record is loaded correctly but the second and third record generates the following error:

Record 2: Rejected - Error on table PROVA, column A.
ORA-01722: numero non valido

Record 3: Rejected - Error on table PROVA, column A.
ORA-01722: numero non valido

The error is generated in my opinion because of PAUL and WHITE is a space and treats them as 2 different fields but the field is unique and is D.

How do you tell him not to consider the space but only the tabs to separate columns?
Thank you very much

Bye


Re: problem using sqlldr(2 Merged) [message #520326 is a reply to message #520325] Sun, 21 August 2011 09:57 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I can not figure out how to release the code tags when I write a message.
see following URL => http://www.orafaq.com/forum/t/171557/136107/

>The error is generated in my opinion because of PAUL and WHITE is a space and treats them as 2 different fields but the field is unique and is D.
Correct, so enclose string in double quote marks.
This forum contain many, many posts which show how to correctly use sqlldr.
SQLLDR is documented in Utilities manual found at http://tahiti.oracle.com
Re: problem using sqlldr(2 Merged) [message #520328 is a reply to message #520326] Sun, 21 August 2011 10:03 Go to previous messageGo to next message
p40l3tt0
Messages: 9
Registered: August 2011
Junior Member
BlackSwan wrote on Sun, 21 August 2011 16:57
>I can not figure out how to release the code tags when I write a message.
see following URL => http://www.orafaq.com/forum/t/171557/136107/

>The error is generated in my opinion because of PAUL and WHITE is a space and treats them as 2 different fields but the field is unique and is D.
Correct, so enclose string in double quote marks.
This forum contain many, many posts which show how to correctly use sqlldr.
SQLLDR is documented in Utilities manual found at http://tahiti.oracle.com



Thanks for the reply.
But why the tab and space consider them equal?
Re: problem using sqlldr(2 Merged) [message #520330 is a reply to message #520328] Sun, 21 August 2011 10:12 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But why the tab and space consider them equal?
>FIELDS TERMINATED BY WHITESPACE
WHITESPACE includes BOTH Horizontal Tab, CHR(9), and space, CHR(32), characters.

if sqlldr is to treat them differently, do NOT use keyword WHITESPACE!
Previous Topic: import
Next Topic: Audit Select..
Goto Forum:
  


Current Time: Thu Mar 28 08:28:14 CDT 2024