Re: Oracle + Unix question , Error validation for SQL statements in co process ?.

From: Arup Nanda <orarup_at_hotmail.com>
Date: Tue, 20 Jul 2004 13:47:25 -0400
Message-ID: <BAY12-DAV15J5h1DCx300007a1f@hotmail.com>

Try using SQL.SQLCODE instead.

sqlplus /nolog << EOF
connect / as sysdba
whenever sqlerror exit sql.sqlcode
select ddd from dual;
exit
EOF
RC=$?
echo Return Code = $RC

Executing the above script produces

SQL> Connected.
SQL> SQL> select ddd from dual

       *
ERROR at line 1:
ORA-00904: "DDD": invalid identifier

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Clusters options JServer Release 9.2.0.4.0 - Production
Return Code = 136

\
----- Original Message -----
From: "Ranjeesh K R." <ranjeeshk_at_infics.com> To: <undisclosed-recipients:>
Sent: Tuesday, July 20, 2004 12:25 PM
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_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
-----------------------------------------------------------------

Received on Tue Jul 20 2004 - 13:06:30 CDT