pass oracle variables to shell script variables

[umeman]

Admins,

Is there a way to set a unix shell variables to oracle 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

 

[drummer0921]

Yes, I believe you just put the variables in the sqlplus in single quotes. Its been a while since I have done it, but it can be done. Try double quoting the variable under sqlplus as well. JW

 

[hronek]

There are a number of ways you can do input and output from sqlplus from a UNIX shell script.

sqlplus scott/tiger@db << EOF > output_file
sqlplus ... ${SHELL_VAR} ...
EOF

or

sqlplus scott/tiger@db @sqlfile.sql ${SHELL_VAR} > output_file

The sqlfile.sql would be your sqlplus code in a file. To pass a variable into sqlplus code you need to use a & symbol in your code much like you would use $ in UNIX.

e.g. of a sqlfile.sql

select ...
where column='&1'
...
exit

You can also use what they call spool in sqlplus to log output to a file. This is done by saying &quot;spool filename&quot; at the begining of your sqlcode.

I hope this helps.