execute PL SQL Procedure from Shell Script

Question

Hi I'm trying to execute PL SQL Procedure from my Shell Script and get the return value (out value), but it's not working. Can anyone advise what I'm doing wrong? Here's what I have:

output="$(sqlplus -S user/pw@//ip:1521/db <<ENDOFSQL
set serveroutput on;
DECLARE
    v_return PLS_INTEGER;
BEGIN
    PKG.Procedure(v_return);
    DBMS_OUTPUT.PUT_LINE(v_return);
END;
exit;
ENDOFSQL)"

echo $output 

Solution

After a long day of trial and error I finally got it working with the below script:

#!/bin/ksh

CODE=`sqlplus -S $SCHEMA/$PW@//$IP_PORT/$DB << EOM
Set timing on
Set serveroutput on
Whenever sqlerror exit failure;
Whenever  oserror exit failure;
declare
v_return number;
begin
PKG.Procedure(v_return);
end;
/
EOM`

if [ $? != 0 ]
then
 echo  "process failed."
 exit 1
fi

exit $?