http://www.orafaq.com/wiki/Unix_FAQ
For returning single value use this ...
yourvalue=`sqlplus -s $LOGON_NAME/$LOGON_PASSWD << EOF
set heading off;
select col2 from table_a where col1 = 10;
exit;
EOF`
For returning multiple values use this ...
sqlplus -s /nolog |& # Open a pipe to SQL*Plus
print -p -- 'connect user/password@instance'
print -p -- 'set feed off pause off pages 0 head off veri off line 500'
print -p -- 'set term off time off'
print -p -- "set sqlprompt "
print -p -- "select sysdate from dual;"
read -p SYSDATE
print -p -- "select user from dual;"
read -p USER
print -p -- "select global_name from global_name;"
read -p GLOBAL_NAME
print -p -- exit
echo SYSDATE: $SYSDATE
echo USER: $USER
echo GLOBAL_NAME: $GLOBAL_NAME
For passing error value back to shell use the following
http://www.sap-img.com/oracle-database/passing-values-back-to-unix-script-from-sqlplus.htm
write following code in sql script
set echo on
whenever sqlerror exit 1
whenever oserror exit 2
--your code--
exit 0
write following code in shell script to receive the value
--call sql script
ret_val=$?
now ret val will have value 0 if sql script did not fail and 1 or 2 otherwise
