Shell Programming and Scripting

I am writing a unix script that logs into oracle and does a count on a table. I want to return that count to my unix script. this is my script:

#!/bin/ksh

typeset retcode;

numin=0
sqlplus -s <<-EOSQL
myuser/mypass@mydb
variable ret_val number;
begin
select count(*) into :ret_val from ofcr_codes;
end;
exit :ret_val
EOSQL
retcode=$?
print "retcode is ${retcode}"
return 0

The retcode is coming up as zero. the retcode should equal 1100, the number of records in the ofcr_codes table. What am I doing wrong?

I'm not sure what your doing wrong, I had the need to do something similiar and instead of manipulating the exit code you could do something like this: note the ticks surrounding the sqlplus and EOF ```

hth

qty=`sqlplus -s $DBLOGIN < < EOF
set head off
set pagesize 0
select count(*)
from ofcr_codes;
EOF`

echo "The Qty is $qty"

or this seemed to work for me, take the spaces out before < EOSQL

#!/usr/bin/ksh

sqlplus -s username/pass < < EOSQL
select count(*) into :ret_val from ofcr_codes;
exit :ret_val
EOSQL
retcode=$?
echo "retcode is ${retcode}"

Thanks the first response worked for me.

qty=`sqlplus -s $DBLOGIN < < EOF
set head off
set pagesize 0
select count(*)
from ofcr_codes;
EOF`

The second one didn't though. I appreciate your help.