Error Handling in .bat File

Question

I have a .bat file which runs two SQL files and moves files around. I need to have some error handling in this .bat file. I want it to take the error generated in sql and pass it into AutoSys I was provided with this code:

#!/bin/sh 
. $HOME/.bash_profile

sqlplus -s $AUTOSYS_DB_USER/$AUTOSYS_DB_PASSWD <<!
WHENEVER OSERROR EXIT 999;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
alter session set nls_date_format='DD/MM/YYYY';
execute P_LOST_APPROVALS;
!

exit_code=$?
if [ $exit_code != 0 ]; then
echo "Error calling sqlplus, return code = " $exit_code
exit $exit_code
fi

exit 0

However, I need it to run in .bat file and not Linux?

I currently have:

echo exit | sqlplus -s %CONNECT% @Code.sql 
IF %ERRORLEVEL% NEQ 0 (
echo Unsuccessful
goto @ERROR_HANDLING_SQL
)

Full .bat file:

@echo off
call config.bat
SET CONNECT=%var1%/%var2%@avpr.world

Pushd  Location


Set SCRIPTPATH=Location
Set RESULTPATH=Location


echo exit | sqlplus -s %CONNECT% @Code.sql
IF %ERRORLEVEL% NEQ 0 (
echo Unsuccessful
goto @ERROR_HANDLING_SQL
)


echo exit | sqlplus -s %CONNECT% @createTXT.sql > File%date:~0,2%%date:~3,2%%date:~6,4%.txt
IF %ERRORLEVEL% NEQ 0 (
echo Unsuccessful
goto @ERROR_HANDLING_SQL
 )



move "File%date:~0,2%%date:~3,2%%date:~6,4%.txt" %RESULTPATH%


echo exit | sqlplus -s %CONNECT% @dropTable.sql
IF %ERRORLEVEL% NEQ 0 (
echo Unsuccessful
goto @ERROR_HANDLING_SQL
)



 : ERROR_HANDLING_SQL
 <echo -1>

pause
Exit

Answer 1

An equivalent for the exit code $? in batch is the errorlevel accessible via the variable %ERRORLEVEL%. In batch scripting, there exists a special form of the IF-clause that uses the errorlevel: IF ERRORLEVEL n is equivalent to: "if the errorlevel is greater or equal to n". As in bash, a value of 0 means the previous command executed without errors (not always, some commands don't set the errorlevel). So you have two choices:

1. IF %ERRORLEVEL% NEQ 0 which is the literal translation of your if-statement in your bash script. Be aware you'll have to use the delayed expansion version IF !ERRORLEVEL! NEQ 0 inside code blocks delimited with (...) (check this question that explains why)
2. IF ERRORLEVEL 1 which is IMHO more suitable (depends on your preferences though). Also works inside (...) code blocks but assumes commands never set negative errorlevels (never encountered one but is worth mentionning)

Normally setting a variable with the set command doesn't change the errorlevel so you can choose either one of the two options. But as I don't have a windows machine to test it right now, I'll play it safe and give you the first option. I'll edit my answer once I've tested the second option.

set exit_code=%ERRORLEVEL%
if %exit_code% NEQ 0 (  
    echo Error calling sqlplus, return code = %exit_code%
    exit /b %exit_code%
)

Some last advice: use exit /b instead of exit. exit will also exit the command line in batch

EDIT I've tested the second version and it works as well. So the code above can be replaced with:

set exit_code=%ERRORLEVEL%
IF ERRORLEVEL 1 (  
    echo Error calling sqlplus, return code = %exit_code%
    exit /b %exit_code%
)

This blog article explains quite well why this version should be used instead of the of the first one using the %ERRORLEVEL% variable.

Another option @aschipfl kindly reminded me of in his comment is the use of the conditional executions operators && and ||:

• command1 && command2 will execute command2 only if command1 succeeded
• command1 || command2 will execute command2 only if command1 failed.

Both operators use the exit code (in most cases the same as errorlevel but not always) to determine if a command failed or succeeded.

I've also seen you've posted your batch-file. There is a little something I'd like to point out though: goto @ERROR_HANDLING_SQL will throw an error because there is no label @ERROR_HANDLING_SQL. There exists a label ERROR_HANDLING_SQL at the end of your script though. If you were trying to jump to that label you should replace all occurences of goto @ERROR_HANDLING_SQL with goto ERROR_HANDLING_SQL