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 ------------------------------