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

Lets simply this by commenting out PKG.procedure(v_return) and replacing it with something static; set it it 1 or something and see if you get some output that way then we can say the issue is with the package. — mmmmmpie
– mmmmmpie, Commented Feb 3, 2015 at 18:32
Thanks for replies. I actually got it to work. I'll answer my own question with my solution. — Whitey Winn
– Whitey Winn, Commented Feb 4, 2015 at 14:43

Whitey Winn · Accepted Answer · 2015-02-04 14:47:56Z

5

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 $?

answered Feb 4, 2015 at 14:47

Whitey Winn

3061 gold badge8 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

execute PL SQL Procedure from Shell Script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related