SQL & PL/SQL

Hi,

I am trying to spool data from tables into flat files. I am using the following scripts to accomplish it

1. A cmd file (windows) that makes a call to a sql file
2. The SQL file which generates another query file at the run time, depending upon the table name passed to it
3. The run time query file , that executes the final query and spools the data into a txt file | delimited

For eg:

Actual command passed C:\Spool_utility\spool_utility TABLE_NAME

Eg of the spool utility file :

@echo off

SET dbuser=XX@YY
SET dbpw=xxxx


echo %date% - %time% - Start > %1%log.txt

echo START

sqlplus -s %dbuser%/%dbpw% @spool_utility.sql %1>%1.txt

echo %date% - %time% - Done >> %1%log.txt
echo DONE

Eg of the spool_utility.sql

set echo off
SET newpage 0
SET feedback off
SET linesize 32767
set pagesize 0
SET wrap off
SET recsep off
SET con off
SET trim on
SET trims on
set verify off
SET doc off
SET termout off
SET arraysize 5000
SET HEADING off
set colsep "|"

spool &1.sql;

Prompt Select * from &1 where some condition

spool off
set termout ON
@ &1

EXIT

The above file generates a table_name.sql file with the actual table name at run time and gets executed and the output is written to the table_name.txt file.

This works perfectly fine. But the issue is when someone passes some wrong table name or if there is a actual run time error while executing the query , the error with details itself itself gets written to the end spool file.

For eg : if i do this just to generate an error and execute it from command line, the query generates an error and writes the error to the spool file , but at the command prompt where I executed the command I do not see any error and the process seems to have run perfectly well

set xxx on xxx off as above

spool &1.sql;

Prompt Select * from &1 where rownum><10---this will cause the issue

spool off
set termout ON
@ &1

EXIT

Eg of spool file generated :

from table_name WHERE rownum><=10 *
ERROR at line 62:
ORA-00936: missing expression

My question is, is there any way i can capture this runtime error and return this error to my calling sql script spool_utility.sql and then propagate it to the calling command file and do some tasks for eg removing the spool file and writing the actual error to a log file . Basically any way to know at my OS calling level that the entire spooling operation was unsuccessful.

Thanks