RE: Oracle + Unix question , Error validation for SQL statements in co process ?.
- From: Kevin Lange <klange@xxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 20 Jul 2004 12:45:52 -0500
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@xxxxxxxxxx]
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)
<<<<<<<<<<<<<<<<<<<<<<<<<<<
cat ${CFG_DIR}/srs_indx.lst | egrep -v '^#' | egrep -v '^[ ]*$' | while
read x
do
..
echo " Processing values :: pre $pre tbl $tbl indx $indx tblspc $tblspc cols
$cols param $param" >> ${LOGS_DIR}/srs_indx.log
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
