How to execute a oracle procedure having out parameter inside a unix script

Question

Can any one please tell me how to execute a simple oracle stored procedure having out parameter inside a shell script.That means it should return a value into the unix environment.

Answer 1

There are a number of ways, but the one I tend to use is illustrated below.

The sqlplus script the_sql_script.sql

var ret varchar2(2000)
exec the_procedure ( the_out_param => :ret );
set pages 0 head off lines 200 trimspool on
spool sqlplus.return
select 'RETURN_VALUE=' || :ret FROM dual;
spool off
quit

In shell:

sqlplus / @ the_sql_script.sql
. ./sqlplus.return
echo $RETURN_VALUE

Answer 2

I assume you want to start a script using SQLPLUS. This answer explains how to assign the value of an out parameter to a bind variable in SQLPLUS.

Call stored procedure from sqlplus

You can exit sqlplus with this value and use that value in the calling script.

exit x

But this usually is restricted to numerical values in a limited range.