Error Handling

Below code works for different databases i.e. MYSQL and ORACLE

The problem is for MYSQL in Block:

if [ $? -ne 0 ]

;

$?

taking value accordingly but in case of ORACLE

$?

is always taking this value as zero (0).

That is the reason in Oracle it always going in else Block in any case..
and in case of MYSQL it is working fine.....

add_fk()
{
echo "`date` Entering $0 " | tee -a $LOGFILE

if [ $DB_SYSTEM = "ORACLE" ]
then
sqlplus -s sna/dbmanager <<! | tee -a $LOGFILE
ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME_FKEY
      FOREIGN KEY (LAYERRATENAME)
      REFERENCES REFERENCE_TABLE(REFERENCE_TABLENAME);
exit
!
     if [ $? -ne 0 ]
     then
    echo "Foreign Key can not be established - incompatibile data" | tee -a $LOGFILE   
    echo " Please verify the data in TABLE_NAME and REFERENCE_TABLE table" | tee -a $LOGFILE
    status=2
     else
    echo "Foreign Key is Added" | tee -a $LOGFILE   
     fi

elif [ $DB_SYSTEM = "MYSQL" ]
then
    mysql --user=${OSMDB_USR} --password=${OSMDB_PWD} ${DB_NAME} <<!
    set storage_engine=INNODB;   
    ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME_FKEY
      FOREIGN KEY (LAYERRATENAME)
      REFERENCES REFERENCE_TABLE(REFERENCE_TABLENAME);
    exit
!
     if [ $? -ne 0 ]
     then
    echo "Foreign Key can not be established - incompatibile data" | tee -a $LOGFILE   
    echo " Please verify the data in TABLE_NAME and REFERENCE_TABLE table" | tee -a $LOGFILE
    status=2
     else
    echo "Foreign Key is Added" | tee -a $LOGFILE   
     fi
fi
}

Please help me:wall:

---------- Post updated at 04:41 PM ---------- Previous update was at 04:12 PM ----------

if [ $? -ne 0 ]

Or please let me know .. what does above meant..?

$? is a special variable which says the previous command status.

most of the commands exit with 0 ( if it is successfully executed )

---------- Post updated at 04:47 PM ---------- Previous update was at 04:44 PM ----------

use the below in your sql

 
whenever sqlerror exit sql.sqlcode 

or

 
exit sql.sqlcode; 

so, your $? variable will have the exit status code.

I implemented the same as you suggested i.e.

whenever sqlerror exit sql.sqlcode 

Sorry to say, but still

$?

is returning zero(0) value...

Kindly, Can you please explain me more about it...

Thanks again...
Ambar

It might be worth trying to replace

sqlplus -s sna/dbmanager <<! | tee -a $LOGFILE

with

(sqlplus -s sna/dbmanager | tee -a $LOGFILE) <<!

What I am thinking might be causing the issue:
Your might be capturing the return value of tee instead of sqlplus... I think that moving the heredoc to the end might result in sqlplus status being in $? when you expect it to.

You might be able to use PIPESTATUS as well, if your shell supports it?

Yes you rightly pointed out the problem ... Thanks Again....

Actually

$?

was returnning the value for the

tee -a $LOGFILE 

which will always successful and it return always zero (0) i.e.

$? = 0 always

...

Once I remove that

tee -a $LOGFILE 

... the code for ORACLE also works perfectly fine now....

Kudos to you....
Ambar