Unable to trap the correct $? from SQL* Loader

[Suman_7]

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

[Paula J Frazer-Campbell]

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

[Indira Aramandla · Accepted solution]

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

[Nicolas Dumeige]

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