SQL Loader - Automation - Exit Codes

Oracle SQL Loader comes with a capability that can be very handy for automating SQL Loader jobs. This feature is "exit codes", which helps to understand the status of the execution for a given job. This can be really useful when we are trying to automate the SQL Loader task. Eventhough this can be read from the log files, reading this information from exit codes can be much faster and eliminates parsing the log file. In this post, we will try to understand what these exit codes mean to us, and how to harness this feature when using it in actual applications.

The following are the exit codes applicable for Unix and Windows platforms:


Unix
Windows
Successful Execution
0
0
An unrecoverable failure has happened
1
3
At least one row got rejected
2
2
Any O/S specific Errors
3
4

The OS specific errors could be errors that happen while opening / reading the file, or memory specific issues.

To read this exit code and handle it in windows, the following batch script can be used:

---------------begin script ------------------------------
sqlldr userid/pwd CONTROL=load.ctl log=load.log
if errorlevel 0 echo SQL*Loader execution successful
if errorlevel 2 echo SQL*Loader got executed, but atleast some rows got rejected, check the log file.
if errorlevel 3 echo SQL*Loader encountered an unrecoverable failure,check the logfile for more details
if errorlevel 4 echo SQL*Loader execution encountered OS Specific Error
---------------end script ------------------------------

1 comment:

  1. In Oracle 11GR2 the codes are as follows:

    EX_SUCC 0
    EX_FAIL 1
    EX_WARN 2
    EX_FTL 4

    http://docs.oracle.com/cd/E14072_01/server.112/e10701/ldr_params.htm

    Also, errorlevels should be tested in descending order:

    http://support.microsoft.com/kb/39585

    ReplyDelete