Re: Call SQL script from Windows .bat file **NEWBIE**

Andrew King (kingster_1969_at_hotmail.com) wrote:
: I am currently trying to call a SQL*Plus script from a Windows batch file.
: I have figured it all out, except for one little piece - I need an error
: code returned to the %errorlevel% environment variable if there is a problem
: in the execution of the script/stored proc.

: This is what I have so far in my batch file:

: set ORACLE_SID=edwm
: set ORACLE_HOME=d:\oracle\ora81ee
: D:
: cd D:\ora\edwm\whs\sql
: d:\oracle\ora81ee\bin\sqlplus username/password @whsverify.sql

: I have seen things in some scripts/stored procs that I have looked at like
: "whenever sqlerror exit SQL.SQLCODE". Will this do what I am looking for?

So test the exit/errorlevel interactively to see if it works.

        C:> sqlplus

        SQL> exit 5

	C:> if errorlevel 6 echo yes
	C:> if errorlevel 5 echo yes
	C:> if errorlevel 4 echo yes

I do not know the results of this test, I can't run it right now.

If the exit code does not turn up as an error level then spool some output into a temporary file and use the FIND command to check the contents of the spool file.

	SQL> spool result.tmp
	SQL> SELECT something FROM somewhere;
	SQL> spool off
	SQL> exit

	C:> FIND "0 rows selected" result.tmp
	C:> if errorlevel 1 goto not_0_rows