Problems with finding errors using errorlevel

Question

I'm having issues with errors one some af my batchjobs. I'm currently running 50 batchjobs each day, with a log file, which tells me if the batchjob was completed successfully or not. The last week i've noticed some errors, and right now, I don't have any control which shows or just tells me if a batchjob fails with an error. My batchjobs are starting af script via sqlplus. I've tried use errorlevel, but id didn't Work.

My batchjob is looking like this so far. I've included the errorlevel code I tried to use.

@Echo Off
@For /F "tokens=1,2,3,4 delims=-/ " %%A in ('Date /t') do @(
Set Day=%%A
Set Month=%%B
Set Year=%%C
Set All=%%A-%%B-%%C
)
@For /F "tokens=1,2,3 delims=:,. " %%A in ('echo %time%') do @(
Set Hour=%%A
Set Min=%%B
Set Sec=%%C
Set Allm=%%A.%%B.%%C
)
@For /F "tokens=3 delims=: " %%A in ('time /t ') do @(
Set AMPM=%%A
@Echo On
)
echo Start: %date% %time%    >> ..\log\Scriptname_%All%_%Allm%.log
sqlplus "Script" >> ..\log\Scriptname_%All%_%Allm%.log
IF %errorlevel% NEQ 0 GOTO :Error
GOTO :Success
:error
echo There was an error.
echo Stop: %date% %time%    >> ..\log\Scriptname_%All%_%Allm%.log
EXIT 1
:end
echo Success.
echo Stop: %date% %time%    >> ..\log\Scriptname_%All%_%Allm%.log
EXIT 0

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

I think sqlplus dosn't by default return error codes so always returns 0.

Some options for changing this are described in this thread:

Sql*plus always returns exit code 0?

Another way is to capture the output and parse it to detect errors.

edited May 23 '17 at 12:20

Community

1
1

answered Jun 26 '14 at 08:30

dethorpe

511
2
9

score 0 · Answer 2 · answered Jun 26 '14 at 10:14

Your batch file looks fine. The problem is most likely with your SQL*PLUS script.

SQL*PLUS does not return with an error by default if an error occurs within the SQL*PLUS script. You can use WHENEVER SQLERROR and WHENEVER OSERROR within your SQL*PLUS script to force termination with a non-zero error code upon error.

whenever sqlerror exit failure
whenever oserror exit failure

There are various options to better control the returned code, and to control the transaction behavior (commit or rollback). Follow the links for more info.

Problems with finding errors using errorlevel

2 Answers2