The thing is, the ?$ will not give you anthing but a 0 when you use it after
the SQL statement because SQLPLUS finish correctly ... even if the SQL that
is ran does not. i.e. The RC of SQLPLUS will always be 0 unless SQLPLUS
itself fails.
I do one of two things when I have to check the return on a sql statement
from an outside running shell script:
1. I redirect output of the SQLPLUS session to a file. Then perform some
kind of search on the resultant file for any ORA- or simular errors.
2. In my procedure or SQL code I write a specific file to the operating
system if there is an error. Then in the shell script I check for the
existance of that file.
-- --Original Message-- --
From: Ranjeesh K R. [mailto:ranjeeshk@(protected)]
Sent: Tuesday, July 20, 2004 11:25 AM
Subject: Oracle + Unix question , Error validation for SQL statements in
co process ?.
Hi,
If I use co process in a shell script , what is the best way to do the error
validation of the execution of any sql statements . I was trying to change
the following code to make use of co process concept. When I tried echo $?
after the execution of a "wrong statements "
it is still giving Zero output ( meaning success)
sqlplus ${BIZSTG} < < EOT > > ${LOGS_DIR}/srs_indx.log
set verify on timing on term on echo on feedback on serveroutput on
WHENEVER SQLERROR CONTINUE
drop index $indx;
WHENEVER SQLERROR EXIT FAILURE
alter session set query_rewrite_enabled = true;
create $pre index $indx on $tbl ($cols)
tablespace $tblspc
$param;
exit
EOT
RC=$?
if ( test $RC -ne 0 )
then
ERR_MSG= "ERROR in creating index $indx for table $tbl from
srs_indx.ksh of $ENVIR : $APP by `whoami`@`hostname` on `date` "
echo $ERR_MSG > > ${LOGS_DIR}/srs_indx.log
process_warning ${LOGS_DIR}/srs_indx.log
exitstat=1
else
echo "$indx created at `date` " > > ${LOGS_DIR}/srs_indx.log
fi
done
> > > > > > > > > > > > > > > > > > > >
Any help will be appreciated .
with thanks and regards
Ranjeesh K R
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
