Wednesday, July 28, 2010

In case you don't know, SQL*loader exit codes have changed

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

A quick posting to report that, for some (good?) reason, Oracle decided to change the values behind the so-called exit codes that SQL*Loader returns upon exit on the Windows platform.

Interestingly enough, this change is actually "documented" because comparing the two pages in the "Utilities" book for Oracle 10gR2 and Oracle 11gR2, they differ slightly as shown below:
Win Oracle 10gR2  Win Oracle 11gR2  Unix
EX_SUCC 0 EX_SUCC 0 EX_SUCC 0
EX_WARN 2 EX_FAIL 1 EX_FAIL 1
EX_FAIL 3 EX_WARN 2 EX_WARN 2
EX_FTL 4 EX_FTL 4 EX_FTL 3
On Unix the exit codes have remained the same across the releases and the only advantage i see in this operation is in the fact that now they are more consistent between the platforms but was this change really worth the effort?

I noticed this because some old scripts, quite nicely, stopped working under certain, non frequent conditions and something remembered me of a similar problem that occurred years ago with SQLPlus.

Practically speaking, a batch program that was stopping for codes greater than 2, now might continue running.
In SQL*Loader terms this means that a failure on an INSERT operation (that requires the target table to be empty) may be not recognized any longer because your script expects a value equal or greater than 2, but now a failure is reported as 1.

So, if you are asking yourself why an old SQL*Loader batch program suddenly doesn't work anymore as before on Windows, then you might have found the answer to the problem.

By the way, I checked the upgrade guide and release notes but i failed to find any entries mentioning changes in SQL*Loader's exit codes.
Or may be i didn't look hard enough?

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles