Reg DB values

UNIX for Advanced & Expert Users
1

Hi,

I would like to call my Data Base procedure through unix,
my procedure returns 4 values i.e. (4 out variables), how to assign 4 out variables to unix variables.

Ex:

xxat_safety_stock_main_p(
retcode => l_retcode
,errbuf => l_errbuf
,p_overwrite => l_overwrite
,p_disable => l_disable
,p_org_file => l_org_file
,p_fallout_file => l_fall_out_file
,p_path => l_outpath );

l_diable,l_org_file, l_fall_out_file, l_outpath variables are my out variables, I want to use the values returned by these variables into unix program .
thanx in advace.

Regards
Kiran Kumar

2

What do you mean by unix program? C? bash? And how is this procedure being called, that's just as important.

3

If you are using an oracle Database then you can use this. Or else you have to use the executable that could execute the peice where this peice of code uses "sqlplus".

The following code should be in your Unix shell script.

PARAMETERS=`sqlplus -s USERNAME/PASSWORD@db_instance << EOF
set space 0;
set pagesize 0;
set heading off;
set tab off;
set echo on;
set feedback off;
whenever sqlerror exit failure;

         declare
           l_disable      &lt;Datatype&gt;;
           l\_org_file     &lt;Datatype&gt;;
           l\_fallout_file &lt;Datatype&gt;;
           l_outpath      &lt;Datatype&gt;;

         begin
           xxat\_safety\_stock\_main_p\(retcode =&gt; l_retcode
                                   ,errbuf =&gt; l_errbuf
                                   ,p_overwrite =&gt; l_overwrite
                                   ,p_disable =&gt; l_disable
                                   ,p\_org_file =&gt; l\_org_file
                                   ,p\_fallout_file =&gt; l\_fall\_out_file
                                   ,p_path =&gt; l_outpath \);

        DBMS\_OUTPUT.PUT_LINE\('DISABLE=' || l_disable || '-' || 'ORG_FILE=' || l\_org_file || '-' || 'FALLOUT_FILE=' || l\_fall\_out_file || '-' || 'PATH=' || l_outpath\);

        end;

EOF`

if [ $? -ne 0 ]
then
echo "Error while getting parameters"
exit 1

else
# These are the parameters returned by the call to your database procedure
DISABLE=`echo $PARAMETER | cut -d "-" -f1 | cut -d "=" -f2`
ORG_FILE=`echo $PARAMETER | cut -d "-" -f2 | cut -d "=" -f2`
FALLOUT_FILE=`echo $PARAMETER | cut -d "-" -f3 | cut -d "=" -f2`
PATH=`echo $PARAMETER | cut -d "-" -f4 | cut -d "=" -f2`

fi