Another approach
Andre Whittick Nasser, November 19, 2001 - 11:14 pm UTC
Here is an alternative to Tom's script, since you seem to have a specific procedure.
If you seem to have a stored procedure like:
----------------------------------------------------
create or replace procedure calc (a number, b number,
c out number, d out number)
is
begin
c:=a+b;
d:=a*b;
end calc;
----------------------------------------------------
You should do this:
----------------- Start of ksh script --------------
#!/bin/ksh
cat << EOF >/tmp/$$.sql
set termout off
set feedback off
set echo off
set serveroutput on
spool /tmp/$$.ksh
declare
a number:=10;
b number:=20;
c number;
d number;
begin
calc (a,b,c,d) -- This is where your proc comes in
dbms_output.put_line('VAR1='||c); -- These are your ksh
dbms_output.put_line('VAR2='||d); -- variable assignments
end;
/
spool off
exit
EOF
# you can choose your authentication scheme
sqlplus username/password@hoststring @/tmp/$$.sql
. /tmp/$$.ksh
echo $VAR1
echo $VAR2
rm /tmp/$$.sql /tmp/$$.tmp
------------------ End of ksh script ---------------
Just a couple of comments:
- Do not forget to "set serveroutput on"
- "spool off" should be inside the sql code, not in the ksh script body
Continuing...
Andre Whittick Nasser, November 20, 2001 - 8:08 am UTC
There was a small error in my script above. A semicolon (;) is missing:
...
begin
calc (a,b,c,d); <-- There should be a semicolon here
dbms_output.put_line('VAR1='||c);
...
Just that...
Thanks !
November 20, 2001 - 8:52 am UTC
see -- we all make typos ;)
yes, my "spool off" should have been in the $$.sql file -- I moved it around and missed it on the cut and paste.