OraFAQ Forum: Unix » SQLLdr Return Code

Home » Infrastructure » Unix » SQLLdr Return Code

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

SQLLdr Return Code [message #98264]

Tue, 08 June 2004 07:49

Ajith
Messages: 13
Registered: May 2003

Junior Member

I am not able to differentiate the SQLLdr return codes.

Consider the 2 scenarios

1. I set my ERROR(in�sqlldr)�to around 10. If the number of records rejected is greater than 10 then I am getting a return code (#?)�of 2.

I want the batch to abort.

2. This is the same retrun code I am getting if the number of records rejected is less than 10.

In this case I just want to give an Error and go ahead with the Load.

Is there anyway I can differentiate the ERRORS? Should I code to look into the log file or is there a better method?

�Ajith

Report message to a moderator

Re: SQLLdr Return Code [message #98265 is a reply to message #98264]

Tue, 08 June 2004 09:13

andrew again
Messages: 2577
Registered: March 2000

Senior Member

See if this helps:

#!/usr/bin/ksh
sqlldr $1 $2 $3 $4 $5 $6 $7 $8 $9 >/tmp/sqlldr.log 2> /tmp/sqlldr.err.log
RETVAL=$?
#                                +-------+-------+------------+
#                                | UNIX  &#124Windows| VMS        
#   +----------------------------+-------+-------+------------+
#   | Successful                 |   0   |   0   | 1          
#   | Unrecoverable failure      |   1   |   3   | %X10000002 
#   | At least one row rejected  |   2   |   2   | 2          
#   | O/S Errors                 |   3   |   4   | 3          
#   +----------------------------+-------+-------+------------+
if [ $RETVAL -eq 2 ]; then
  exit 0;
else
  exit $RETVAL;
fi

Report message to a moderator

Re: SQLLdr Return Code [message #98280 is a reply to message #98265]

Fri, 18 June 2004 04:57

Adrian Billington
Messages: 139
Registered: December 2002

Senior Member

Andrew's solution cannot be used as you might have reached your limit as you rightly mention in your question. In which case you would have a partial load.

This is a common problem. You'll need the following in your shell script:-

1) to know how many bad records are acceptable;
2) trap the return code 2;
3) grep the number of errors from your logfile ( or count the badfile records if you prefer );
4) test the badfile count against the acceptable bad limit;
5) if bad limit reached, then exit 2, else exit 0.

It's not perfect, but it's the only way I know of because Oracle uses the same return code to mean lots of things.

Regards
Adrian

Report message to a moderator

Re: SQLLdr Return Code [message #98287 is a reply to message #98280]

Thu, 24 June 2004 12:53

Ajith
Messages: 13
Registered: May 2003

Junior Member

Thanks !!!
I had to refer the log files to catch this Error.

Ajith

Report message to a moderator

Previous Topic:	'sf' files in /tmp directory in AIX - what causes them
Next Topic:	unix vs linux

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 09:31:24 CDT 2024