| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Oracle + Unix question , Error validation for SQL statements in co process ?.
If SQLPlus is being used to execute the SQL statements then perhaps "whenever sqlerror exit FAILURE" can be used to return 1 from SQLPlus so that the failure can be detected in the shell.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Kevin Lange
Sent: Tuesday, July 20, 2004 1:46 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: Oracle + Unix question , Error validation for SQL
statements in co process ?.
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:
-----Original Message-----
From: Ranjeesh K R. [mailto:ranjeeshk_at_infics.com]
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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Tue Jul 20 2004 - 13:05:30 CDT
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org 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_at_freelists.org 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
-----------------------------------------------------------------
 |
 |