OraFAQ Forum: Server Utilities » SQL LOADER: format of an error returned by a command line

Home » RDBMS Server » Server Utilities » SQL LOADER: format of an error returned by a command line (oracle 10g)

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

SQL LOADER: format of an error returned by a command line [message #502274]

Tue, 05 April 2011 09:24

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

Report message to a moderator

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

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

Report message to a moderator

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

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

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

Report message to a moderator

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

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

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.

Report message to a moderator

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

cookiemonster
Messages: 13904
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.

Report message to a moderator

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

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.

Report message to a moderator

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

cookiemonster
Messages: 13904
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.

Report message to a moderator

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

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.

Report message to a moderator