General
cancel
Showing results for 
Search instead for 
Did you mean: 

Unable to trap the correct $? from SQL* Loader

SOLVED
Go to solution
Suman_7
Frequent Advisor

Unable to trap the correct $? from SQL* Loader

All,

I am trying to trap the exit status after executing sql*loader.

Although the script fails and writes the failure message in the log file. The exit status '$?' comes out to 0.

Is there anyway I can trap the correct exit status so I may know whether sql*loader has executed successfully or not? I don't want to go the log file and grep for error.

Thank You,
Suman
3 REPLIES
Paula J Frazer-Campbell
Honored Contributor

Re: Unable to trap the correct $? from SQL* Loader

Hi

The script itself can generate a failure message and becouse it has done its programmed task (even though it has generated an error) can exit with a 0 (zero) error and be correct.

Paula
If you can spell SysAdmin then you is one - anon
Indira Aramandla
Honored Contributor
Solution

Re: Unable to trap the correct $? from SQL* Loader

Hi Suman,

The return code of sql*loader will return zero as it has completed its task and exited. If there are error while loading the data then the log file will tell you the error detals as to how many records loaded and how many records rejected. And there will be a bad file in the directory with the rejected data records.

So it is not possible to get the return code of an sql*loader to find it there are errors or not. The bestway will be to check the log file.

Oherwise if you need to know if there are error without checking the log file you can check for an existence of a bad file with extension (.bad) in the directory and this will tell you that there was an error while loading. This can be done by the script automatically. Check for existence of bad file and echo error or send auto e-mail.

I hope this helps.

Indira A
Never give up, Keep Trying
Nicolas Dumeige
Esteemed Contributor

Re: Unable to trap the correct $? from SQL* Loader

Hello,


Check your OracleX Database Utilities documentation :

Exit Codes for SQL*Loader
All rows loaded successfully EX_SUCC
All or some rows rejected EX_WARN
All or some rows discarded EX_WARN
Discontinued load EX_WARN
Command-line or syntax errors EX_FAIL
Oracle errors nonrecoverable for SQL*Loader EX_FAIL
Operating system errors (such as file open/close and malloc) EX_FAIL

For UNIX, the exit codes are as follows:
EX_SUCC 0
EX_FAIL 1
EX_WARN 2
EX_FTL 3

Oracle offers a script to test the return code :
#!/bin/sh
sqlldr scott/tiger control=ulcase1.ctl log=ulcase1.log
retcode=`echo $?`
case "$retcode" in
0) echo "SQL*Loader execution successful" ;;
1) echo "SQL*Loader execution exited with EX_FAIL, see logfile" ;;
2) echo "SQL*Loader execution exited with EX_WARN, see logfile" ;;
3) echo "SQL*Loader execution encountered a fatal error" ;;
*) echo "unknown return code";;
esac

But, I'm with Indira on this, it's best to check the log and look for specific errors. Espacially if you exepect normal discard, head / footer ...

Cheers

Nicolas
All different, all Unix