pass oracle variable values to shell script variables

[umeman]

DBA's

Is there a way to set a unix shell variables to sqlplus
variables.

The following sql called in a shell sript
and I want to capture the output variables err_num and err_mesg into shell script variables:


#!/bin/ksh
filename='test.txt'
err_num=''
err_mesg=''

sqlplus -s scott/tiger@orcl <<EOF
var serr_num varchar2(10);
var serr_mesg varchar2(20);
set serveroutput on;
exec extr_dps_orders('$filename',:serr_num,:serr_mesg);
exit;
EOF
exit
EOF
?? how do I set err_num and err_meg

Thanks
Umesh

 

[rcurva]

I do't know of a quick way to do it, but definitely there is a workaround. Create a separate shell script that runs your procedure, redirecting the results to a log. From a main script call your proc script and afterwards read the log generated for errors or something like that.

This is an idea, I suspect there are lots of ideas out there.

Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War