Home » RDBMS Server » Server Utilities » SQL LOADER: format of an error returned by a command line (oracle 10g)
SQL LOADER: format of an error returned by a command line [message #502274] Tue, 05 April 2011 09:24 Go to next message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
Hi everyone,

I'm working with sqlldr and i try to insert data from a csv file thanks to a CTL file.
One field of my table contains 5 characters but one row has 6 characters in this field, so it's rejected by oracle. (Logicial, you can't insert 6 chars in a 5 chars field)

an error is visibly returned, so i wondered how you could catch the value of this error?
is it a code? a message?

I'd like to add to my script a condition so that the end of the script would continue even if this error code is returned for that CTL execution....

I don't know if i'm clear.
Please do not hesitate to ask for more details.

Thanks a lot
Re: SQL LOADER: format of an error returned by a command line [message #502276 is a reply to message #502274] Tue, 05 April 2011 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 26757
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#sthref710

upon success, the the length of BAD file should be zero
Re: SQL LOADER: format of an error returned by a command line [message #502284 is a reply to message #502276] Tue, 05 April 2011 09:50 Go to previous messageGo to next message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
I could of course know if there has been an error with a bad file, but i was more wondering what string was returned after executing the command line.
And what was specifically the format of those returns...
It returns 0 if everything went fine but what are the other kind of returns?

How may i be more helpful BlackSwan ?

[Updated on: Tue, 05 April 2011 09:50]

Report message to a moderator

Re: SQL LOADER: format of an error returned by a command line [message #502286 is a reply to message #502284] Tue, 05 April 2011 09:52 Go to previous messageGo to next message
BlackSwan
Messages: 26757
Registered: January 2009
Location: SoCal
Senior Member
>How may i be more helpful BlackSwan ?
Construct test scripts that throw errors & see for yourself what is returned!
Re: SQL LOADER: format of an error returned by a command line [message #502288 is a reply to message #502286] Tue, 05 April 2011 10:08 Go to previous messageGo to next message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
Thanks for your wonderful help and for your warm kindness.

Actually the only error message i can get for now is in the error file, but the problem is that the message is really specific to the field causing the problem. so i can't really use that since i can't write one condition dor each of my 70 field...
i'm launching a ksh script and can't really see a specific error message aside the one in the error fields.

Thanks anyway

[Updated on: Tue, 05 April 2011 10:08]

Report message to a moderator

Re: SQL LOADER: format of an error returned by a command line [message #502297 is a reply to message #502288] Tue, 05 April 2011 10:53 Go to previous messageGo to next message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
With sqlldr, when you try to insert (with a ctl file and by using a command line on unix) a row with a field having too many characters compared to what is set on the table created, the command line returns '2' at the end of the treatment.
But, even though it returns 2, it doesn't mean that every good formatted rows didn't get treated.
So, the row is just rejected, but every good row have been inserted.
(If everything works (if evry row has the good format) it returns 0)

the problem is: what does this "2" returned mean?
Is it a code for only this kind of error?
What are the other possible values?

Thanks a lot.
Re: SQL LOADER: format of an error returned by a command line [message #502300 is a reply to message #502297] Tue, 05 April 2011 11:11 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
I suspect you get 2 for all data errors. There are so many possible ones oracle was never going to bother doing a unix return code for each, that's why they have the bad and log files.
Re: SQL LOADER: format of an error returned by a command line [message #502370 is a reply to message #502300] Wed, 06 April 2011 02:29 Go to previous messageGo to next message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
Thanks a lot, that's what i wanted to know actually.
I undertsand there's a lot of possible errors.
I lauch ctl treatments and sql file execution from a ksh file, so i don't really know how i could catch the error.
For the moment, for each different treatment, i catch the return value
If it's 0 then i continue the script, and lauch the next treatments
If not then i launch a back sql file

It seems i won't be able to put a use case for the value '2' if that value isn't linked to this kind of error....

which means i can't do much except hoping the csv files have a proper format?

Thanks a lot cookie.

Re: SQL LOADER: format of an error returned by a command line [message #502386 is a reply to message #502370] Wed, 06 April 2011 03:18 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you use external tables instead of sqlloader. Then you can use pl/sql to check for all possible errors.
Re: SQL LOADER: format of an error returned by a command line [message #502390 is a reply to message #502386] Wed, 06 April 2011 03:31 Go to previous messageGo to next message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
Interesting....
I thought a lot of this solution but my company doesn't really like the idea....
There are quite sceptical concerning the synchronisation between the requests and the file content. I don't really understand why but... whatever.

What would happen if you create an external table linked to a csv file with a format error on a row?
Won't it blow up everything?
Whats would happen if you make a select on that row?

Thanks a lot.
Re: SQL LOADER: format of an error returned by a command line [message #502396 is a reply to message #502390] Wed, 06 April 2011 03:39 Go to previous messageGo to next message
cookiemonster
Messages: 13895
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're worried about format then you just create every column in the external table as a long varchar2 then check formatting after reading it.
Re: SQL LOADER: format of an error returned by a command line [message #502400 is a reply to message #502396] Wed, 06 April 2011 03:49 Go to previous message
Benjamin_Paris
Messages: 21
Registered: March 2011
Location: Paris
Junior Member
Yes i guess i could do that.
Thanks a lot cookiemonster!!
Previous Topic: SQLLoader: Condition on field [country Like '%String%']
Next Topic: IMPORTING DUMP FILE
Goto Forum:
  


Current Time: Thu Dec 03 23:38:03 CST 2020