CODEWITHSUNDEEP
databaseoracle-databaseoracle10gsqlplusdatabase-agnostic
Rajubhai
Rajubhai

Reputation: 107

Stop execution of batch file on error for sqlplus in oracle

I am having 4 files named test.bat which contains

@echo off
C:\oraclexe\app\oracle\product\10.2.0\server\BIN\sqlplus -s -l user/pass@localhost @E:\Oracle_Files\query.sql>E:\Oracle_Files\error.txt;
C:\oraclexe\app\oracle\product\10.2.0\server\BIN\sqlplus -s -l user/pass@localhost @E:\Oracle_Files\query1.sql>E:\Oracle_Files\error.txt

Now query.sql contains

WHENEVER SQLERROR EXIT SQL.SQLCODE
insert into mytable12 values('d');
exit;

And query1.sql contains

WHENEVER SQLERROR EXIT SQL.SQLCODE
begin
insert into mytable1 values('d2');
end;
exit;

Now the problem is REAL TABLE NAME is mytable1,so here problem is I want to stop execution of batch file as soon as error came below scripts on error should not get executed, and also on error errorlog.txt file content gets replaced with "1 row updated" that means my previous error message are getting overwrite, how can I stop this ?

In short there are 2 issues

Upvotes: 1

Views: 5923

Answers (1)

Brad Bruce
Brad Bruce

Reputation: 7817

You need to check the errorlevel after each call to SQL*Plus

@echo off
C:\oraclexe\app\oracle\product\10.2.0\server\BIN\sqlplus -s -l user/pass@localhost @E:\Oracle_Files\query.sql>E:\Oracle_Files\error.txt;
if errorlevel 1 (
  goto error
)
C:\oraclexe\app\oracle\product\10.2.0\server\BIN\sqlplus -s -l user/pass@localhost @E:\Oracle_Files\query1.sql>>E:\Oracle_Files\error.txt
    if errorlevel 1 (
      goto error
    )
:: no error, skip error message
    goto end
    :error
      echo error occurred - check log file
    :end

Upvotes: 3

Related Questions