runnin g SQL from a .bat 1

[Cretin]

We are using DOS .bat files to run SQL, (don't blame me I suggested otherwise but this the way it is). It runs fine, however when the SQL fails due to a record issue the .bat does not pick up the error and continues processing. here is a sample of the code:

sqlplus -l -s user@database @job_run.sql  
  echo error level is %errorlevel%

    REM  Check for error in executing job
    if %ERRORLEVEL% neq 0 goto error_574 
    if %ERRORLEVEL% equ 0 goto no_error_574
       
 

:error_574

        echo there was an error

     :no_error_574

     echo no_error_574

Here are the echos on the screen

*
ERROR at line 1:
ORA-20000: step: 01, Process aborted due to ORA-29283: invalid file operation
ORA-06512: at "job_step", line 488
ORA-29283: invalid file operation. Error number: -29283
ORA-06512: at "job_run.sql", line 766
ORA-06512: at line 1

error level is 0
no_error_574
there was no error.

Is what I am trying to find out is how to translate a failed SQL into an %errorlevel% in .bat that is greater than 0. Any suggestions?

Cretin

 

[Cretin]

I am trying this in the SQL

whenever sqlerror exit failure;

Unfortunately I get the following error on compile:

Error(49,19): PLS-00103: Encountered the symbol "EXIT" when expecting one of the following: := . ( @ % ; not null range default character The symbol ";" was substituted for "EXIT" to continue.

Any ideas?



Cretin

 

[Thargy]

Cretin,

I use a bat file to launch entire database build scripts, upgrade scripts and other utilities, and have done so for years with great success.

A scripted utility should always be one that will finish error free. There is no really good way of propagating errors out from oracle to DOS.

When you said that it fails due to a record issue, can you be a bit more specific? The trick is to only run scripts which are thoroughly tested, known and guaranteed to be good. Scripts are intended to reproduce a fixed set of actions. They are not normally user interactively, other than to provide simple prompts and receive a few variables.

Regards

T

 

[Cretin]

A record in the file will have bad data. It causes the SQL to fail but the subsequent tasks in the .bat still continue to run. When I try to put the statement

WHENEVER SQLERROR FAILURE

in the SQL in hopes of capturing the error code I get the error Error(49,19): PLS-00103: Encountered the symbol "EXIT" when expecting one of the following: := . ( @ % ; not null range default character The symbol ";" was substituted for "EXIT" to continue. I get a similar message when I put in

 WHENEVER SQLERROR 1

. I get the messages at compile time.
I do have the code in the .bat

if %ERRORLEVEL% neq 0 goto error_574 
    if %ERRORLEVEL% equ 0 goto no_error_574

The way it works is Grid releases the .bat, which releases the SQL. Is what I would like to do is get the .bat to recognize if the SQL goes down and then pass that to Grid so the job ends without processing any other subsequent steps.

Cretin

 

[Thargy]

Cretin,

this is a classic case of the need for data cleansing.

You should first run a script which will detect and log all the errors in data. These may then either be moved off to a separate table for handling, or corrected by running a cleansing script.

Your main script should always run error free.
First run an error detection script, fix the problems, and then your main routine will always work.

Regards

T

 

[Turkbear]

Hi,
As has been posted:

There is no really good way of propagating errors out from oracle to DOS.

your DOS program will not be able to 'see' the Sqlerror result and so the %ERRORLEVEL% code is not invoked .

Here is a site with full details on what WHENEVER SQLERROR can and cannot do - maybe a revision of your code can help?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch12052.htm

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Cretin]

We were able to get the process to work. You can't capture an error code in an oracle database with a .bat. But is what you can do is create an sql in the .bat that ends up running the sql in the database. That sql can then capture the error from the sql in the database and pass it to the .bat. Example:

  echo Creating test_SQL_File >> %V_EXE_LOC%\log_test_%V_RUN_DATE_TIME%.log

    echo spool %V_EXE_LOC%\test.%V_RUN_DATE_TIME% >%V_test_SQL_FILE%
    echo set serveroutput on size 1000000 >>%V_test_SQL_FILE%
    echo WHENEVER SQLERROR EXIT SQL.SQLCODE >>%V_test_SQL_FILE%
 echo exec test >>%V_test_SQL_FILE%
    echo spool off >>%V_test_SQL_FILE%
    echo exit >>%V_test_SQL_FILE%

Then

if errorlevel 1 goto error_test 
    if errorlevel 0 goto no_error_test

It works like a champ we run it from Grid it returns an error code and fails.

Thanks for the help.

Cretin

 

[KenCunningham]

A star for posting back with your solution, Cretin.

The internet - allowing those who don't know what they're talking about to have their say.