return code

[Naeem]

Hello Gurus
Oracle version 10g
server Solaris
Client XP
I am executing a script sql from a bat file which is as follows

sqlplus par/fra@test @c:\ora_scr\REFRESH_IRT.sql
echo errorlevel

and my sql file is

SET SERVEROUTPUT ON
set echo off
set feedback off
set heading off
SET TERMOUT OFF
set linesize 1000


var rc number;

WHENEVER SQLERROR EXIT :rc

SPOOL C:\ora_scr\FPT_IRT_DROP_COPY.SQL

exec :rc :=1;
SELECT 'DROP TABLE COPY_'||TABLE_NAME|| ' ;'
FROM USER_TABLES WHERE TABLE_NAME LIKE 'IRT%'
LAND TABLE_NAME != 'IRT_SCHED';

SPOOL OFF

I am trying to get the return code from the sqlplus to msdos.
But after the sql error my dos promt disappears completely and I am unable to see the return code
Any body has a solution for this
Thanks for your help

[ebrian]

Try to put %'s around errorlevel:

sqlplus par/fra@test @c:\ora_scr\REFRESH_IRT.sql
echo %errorlevel%

and place an exit at the end of your sql script:

SET SERVEROUTPUT ON
set echo off
set feedback off
set heading off
SET TERMOUT OFF
set linesize 1000

var rc number;

WHENEVER SQLERROR EXIT :rc

SPOOL C:\ora_scr\FPT_IRT_DROP_COPY.SQL

exec :rc :=1;
SELECT 'DROP TABLE COPY_'||TABLE_NAME|| ' ;'
FROM USER_TABLES WHERE TABLE_NAME LIKE 'IRT%'
LAND TABLE_NAME != 'IRT_SCHED';

SPOOL OFF
exit

[Naeem]

Thanks for your reply.