How to get OUT parameter of a stored procedure in shell script?

Shell Programming and Scripting
1

I am invoking a SQL script from shell script. This SQL script will invoke a stored procedure(which has the OUT parameter). I want to have the OUT parameter in the shell script as a variable. Is this possible?

2

Modify the stored procedure or write a pl/sql wrapper.
assume your stored procedure is: foo(inputparm, output)
example wrapper:

#!/bin/bash
input="some data"
output=$(
  sqlplus -s username/passwd@dbname <<EOF
  set serverout on size 1000000
  set feedback off
  DECLARE
     dbms_output.enable(1000000);
     local_input varchar2(256):="$input";
     local_output varchar2(256):=NULL;
  BEGIN
     foo(local_input, local_output);
     dbms_output.print_line(local_output);
  END;
/
EOF
)
3

I have got the below error when i followed the above method

ERROR at line 2: ORA-06550: line 2, column 12: PLS-00103: Encountered the symbol "." when expecting one of the following: constant exception <an identifier> <a double-quoted delimited-identifier> table LONG_ double ref char time timestamp interval date binary national character nchar The symbol "<an identifier>" was substituted for "." to continue.

---------- Post updated at 10:45 AM ---------- Previous update was at 10:37 AM ----------

I am able to execute the procedure after removing
dbms_output.enable(1000000);
but i didn't get the OUT parameter in the shell variable output. Please help.

---------- Post updated at 11:05 AM ---------- Previous update was at 10:45 AM ----------

I am almost there with the below shell & sql script. Its returns a NUMBER value, but i can't able to use this a variable in shell. Please see the output and suggest a solution

Shell

#!/bin/sh
value=`sqlplus -S $username/$password@$tnsname @/loc/temp.sql << EOF`
echo $value
if [ $value -gt 0 ]
then
echo "value is Greater"
done

SQL Script

set pages 0
set trimspool on
set serveroutput off
set echo off
set feedback off
set heading off
variable out NUMBER
begin
        pack.proc(:out);
end;
/
print :out
exit;

O/P
20005
temp.sh: line 8: syntax error: unexpected end of file

4

Try it without the " << EOF":

#!/bin/sh
value=`sqlplus -S $username/$password@$tnsname @/loc/temp.sql << EOF`
echo $value
if [ $value -gt 0 ]
then
  echo "value is Greater"
done
5

I have removed "<< EOF" but still getting the same error.

value=`sqlplus -S $username/$password@$tnsname @/loc/temp.sql`
6

Try this at the sql prompt:

desc dbms_output
-- if this fails try:
desc sys.dbms_output

This has been a standard package in Oracle since v8. If you not see it, either the DBA set it up incorrectly, or you do not have it for some reason.

If desc sys.dbms_output works, add the owner, sys., to the front of dbms_output everywhere in the code I gave you. It does work.

Let us know. The next step is either to get DBA cooperation or create a ridiculous hack.
And the hack assumes your oracle userid has create table or owns a table that is meant as temp storage.

7

Thanks all helping me out here. I have written the OUT value in a file and then get the same by reading the written file. It worked.