Assigning return value of an embedded SQL in a shell script variable

Shell Programming and Scripting
1

I've a script of the following form calling a simple sql that counts the no of rows as based on some conditions. I want the count returned by the sql to get assigned to the variable sql_ret_val1. However I'm finding that this var is always getting assigned a value of 0. I have verified by executing the sql statement via sqlplus that I am indeed having 1 being returned as a count with my given conditions.

#!/bin/ksh
export SUCCESS=0
export ERROR=1
...
typeset -i sql_ret_val1=`sqlplus -s ${ORA_CONN_STR} <<EOF > /dev/null
SET HEADING OFF

SELECT COUNT (1) FROM <table> WHERE <condition1> AND <condition2>;

EOF`

if [ ${sql_ret_val1} -ne 0 ]
then
   echo "SQL returned count is: ${sql_ret_val1}"
   mailx -s "Values found" ${MAIL_RECIP} < ${LOG_FILE}
   return ${ERROR}
else
   echo "SQL returned count is: ${sql_ret_val1}"
   mailx -s "No values found" ${MAIL_RECIP} < ${LOG_FILE}
   return ${SUCCESS}
fi
...

Any suggestions?

---------- Post updated at 15:06 ---------- Previous update was at 14:56 ----------

Guys, you can ignore the above post. I myself realized that the problem was due to the following:

typeset -i sql_ret_val1=`sqlplus -s ${ORA_CONN_STR} <<EOF > /dev/null

Which when corrected as following, started to work:

typeset -i sql_ret_val1=`sqlplus -s ${ORA_CONN_STR} <<EOF
2

Hi.

You have thrown the output away (to /dev/null), and one or two more sets wouldn'g go amiss:

Change:

typeset -i sql_ret_val1=`sqlplus -s ${ORA_CONN_STR} <<EOF > /dev/null
SET HEADING OFF

To:

typeset -i sql_ret_val1=`sqlplus -s ${ORA_CONN_STR} <<EOF
SET HEADING OFF FEEDBACK OFF PAGESIZE 0

---------- Post updated at 11:39 AM ---------- Previous update was at 11:37 AM ----------

Ah. Didn't see your update :smiley:

1 Like