Hi Friends,
I am connecting to database from shell script and executing sql statements.
The code works fine if i pass a SELECT statement but it fails when pass an UPDATE statement.
The value of $? is 0 when i pass SELECT statement but the value is 1 when i pass UPDATE statement and hence my program completes with ERROR.
When we are storing sqlplus output into a shell variable, do the sqlplus need to return a value always?
Because in my scenario, the SELECT statement will print the result onto the screen and this value gets stored into a shell variable, but in UPDATE, nothing is printed on the screen and hence nothing is passed to shell variable.
Can any one tell how to overcome this?
Session output for SELECT statement:
/home/applas> cat msr3.sh
FCP_LOGIN=APPS/XXXXXX
vg_sql_stmt="SELECT TO_CHAR(NVL(MAX(TRANSACTION_ID),0)) FROM PO_3PL_ASN_
LAST_TXN;"
vg_result=`sqlplus -s /nolog <<EOF | egrep -v '^Connected.$'
connect ${FCP_LOGIN}
WHENEVER SQLERROR EXIT 1
SET HEADING OFF
SET PAGESIZE 0
SET FEEDBACK OFF
${vg_sql_stmt}
EOF`
echo "return_code="$?
echo "vg_result="$vg_result
/home/applas> sh msr3.sh
return_code=0
vg_result=14798281
you have mail in /var/mail/applas
/home/applas>
Session output for UPDATE statement:
/home/applas> cat msr4.sh
FCP_LOGIN=APPS/XXXXXX
vg_sql_stmt="UPDATE PO_3PL_ASN_LAST_TXN A SET A.TRANSACTION_ID = NVL((SE
LECT Max(B.TRANSACTION_ID) FROM PO_3PL_ASN_TEMP B), A.TRANSACTION_ID);"
vg_result=`sqlplus -s /nolog <<EOF | egrep -v '^Connected.$'
connect ${FCP_LOGIN}
WHENEVER SQLERROR EXIT 1
SET HEADING OFF
SET PAGESIZE 0
SET FEEDBACK OFF
${vg_sql_stmt}
EOF`
echo "return_code="$?
echo "vg_result="$vg_result
/home/applas> sh msr4.sh
return_code=1
vg_result=
/home/applas>
Regards,
Sreekanth