1

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

  • how to stop further execution of scripts on error in particular 1 script file.
  • How to prevent from overwriting of logs in log file
Maheswaran Ravisankar
  • 17,652
  • 6
  • 47
  • 69
Rajubhai
  • 107
  • 4
  • 13
  • 1
    For issue with overwritten logs , you may need to replace the `>` with `>>` which will append the content and not overwrite. – Maheswaran Ravisankar Jan 04 '14 at 17:04
  • Ok in that case this message is getting print : insert into mytable12 values('final1') * ERROR at line 1: ORA-00942: table or view does not exist 1 row created. But now the problem is I dont want 1 row created success message only if error message is there then it should write..what could be done for that?? – Rajubhai Jan 05 '14 at 03:42
  • set feedback off; will not display 1 row created. But I beleive cant you do a pl/sql with utl_file instead? if you want a better control of how to handle things. – Maheswaran Ravisankar Jan 05 '14 at 03:54
  • also one more thing how to clear contents of log file before start? – Rajubhai Jan 05 '14 at 03:59
  • 1
    run ur first script with '>' as log redirection. it automatically creates a empty file and overwrites.. – Maheswaran Ravisankar Jan 05 '14 at 04:01

1 Answers1

3

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
Brad Bruce
  • 7,638
  • 3
  • 39
  • 60
  • If you check for errorlevel 1, you will have to make sure sqlplus exit with value 1: whenever sqlerror exit 1; I think this is platform dependent. – Bjarte Brandt Jan 04 '14 at 19:06
  • Based on the @echo off and the use of Oracle XE. I feel pretty sure that it's on Windows. The check for errorlevel 1 will pick up errorlevel values of 1 or greater. – Brad Bruce Jan 04 '14 at 19:09
  • Ok in that case this message is getting print : insert into mytable12 values('final1') * ERROR at line 1: ORA-00942: table or view does not exist 1 row created. But now the problem is I dont want 1 row created success message only if error message is there then it should write..what could be done for that?? – Rajubhai Jan 05 '14 at 03:53
  • Add "set termout off" at the beginning of your SQL script. (without the quotes) – Brad Bruce Jan 05 '14 at 20:41