Operating System - HP-UX
1748038 Members
4940 Online
108757 Solutions
New Discussion

Re: Returning result of an Oracle stored procedure to a shell script

 
SOLVED
Go to solution
Dave Burton_1
New Member
Solution

Re: Returning result of an Oracle stored procedure to a shell script

To pass an output parameter from a stored proc directly to a shell script variable, you can use the following technique.

#!/bin/sh

SHELL_VAR=`sqlplus -s un/pw <set feedback off pages 0
var plusvar varchar2(100)
exec stored_proc_name (:plusvar)
print plusvar
EOF`

echo ${SHELL_VAR}
# or process SHELL_VAR according to your needs

This way, you won't have to rework your stored proc to include dbms_output statements, you can use it just as it is. Also, you can return character values - SQL*Plus exit restricts you to numeric values only.
Jeanine Kone
Trusted Contributor

Re: Returning result of an Oracle stored procedure to a shell script

Thanks Everyone - I got what I needed.

Simon - yours did work, and got me part way there. It would have been an okay workaround if I needed to.

Mark - yours also would have worked if I could not get exactly what I wanted (returning different text statuses).

Dave - Exactly what I needed.

For future reference, here is what I ended up with:

the shell script:

VALUE=`$ORACLE_HOME/bin/sqlplus -s user/pass @sqlscript`

if [ "$VALUE" = 'ERROR TYPE 1' ];
then
code to process here;
else
code to process here;
fi

the sql script:

set pagesize 0
set feedback off
set verify off
set heading off
set echo off

var out_status varchar2(30)
exec sp_tester(:out_status);
print out_status
exit;

the stored procedure:

procedure sp_tester (out_status out varchar2) is
begin
select 'ERROR TYPE 1' from dual;
end;

Thanks again everyone.