Shell Programming and Scripting

Hi All,

Does anyone have any solutions for passing back multiple variables back to the SHELL from a call to an ORACLE procedure:

eg
#username='scott'
#password='tiger'
#database='orcl'
username='ITGCD03D03'
password='tC5epIew'
database='ITGCD03D'


sqlplus -s $username/$password@$database<<EOF

set serveroutput on size 1000000
set verify off

VARIABLE vi_err NUMBER
VARIABLE val_err NUMBER;

begin

package.procedure(p_err_out => :vi_err
,p_validation_err_out => :val_err);

dbms_output.put_line('val_err='||:val_err);

end;
/

EXIT:vi_err
EOF

print out values of Vi_err and val_err here????

any feedback would e greatly appreciated!
Kind Regards
Satnam

I don't get it. Are you talking about reading the output of the oracle procedure into shell variables?

Try something like this:

Code:

a=$(sqlplus -s $username/$password@$database<<EOF 

set serveroutput on size 1000000
set verify off

VARIABLE vi_err NUMBER
VARIABLE val_err NUMBER;

begin 

package.procedure(p_err_out => :vi_err
,p_validation_err_out => :val_err);

dbms_output.put_line('val_err='||:val_err); 

end; 
/ 

EXIT:vi_err
EOF
)

Whatever is output by the stuff between the parens will be stored in $a. You can then work on $a to get the values that you need.

HI MATE

yes what i was getting at was tryin to pass 2 out parameters so that thay can be individually accessed in back in the shell script.

o in the the example I gave, the oracle packaged procedure is executed returning somthing in its OUT parameters :vi_err and :val_err.

The value of :vi_err can be accessed as it is returned in the EXIT command
but Im not sure how to access the value of :val_err.

I could have used a function as suggested but that would only return the 1 variable not both as i would like.. does that make sense?

Kind Regards

Satnam

No need to use exit or any such thing. If you can just get the procedure to print these two values, that should be enough for the shell. The two values will be stored in the variable ($a in my example). Then you can use parameter expansion in bash/ksh to get the two values seperately.

$ a=($(printf "%s;\n" "set pages 0 serverout on feed off" \
> "exec dbms_output.put_line('Line1')" \
> "exec dbms_output.put_line('Line2')" \
> | sqlplus -s / as sysdba))
$ echo ${a[0]}
Line1
$ echo ${a[1]}
Line2

or:

$ printf "%s;\n" "set pages 0 serverout on feed off" \
> "exec dbms_output.put_line('Line 1')" \
> "exec dbms_output.put_line('Line 2')" \
> | sqlplus -s / as sysdba \
> | { read var1;
> read var2;
> echo "var1 is $var1 , var2 is $var2"
> }
var1 is Line 1 , var2 is Line 2


Regards
Dimitre