Skip to Main Content
  • Questions
  • How to bind output Parameter from PL/SQL into KSH script

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Gyanu.

Asked: November 19, 2001 - 10:52 am UTC

Last updated: November 20, 2001 - 8:52 am UTC

Version: 8.1

Viewed 1000+ times

You Asked

I have KSH script which executes P/SQL procedure and returns few parameters. After I exit from SQLPLUS environment I want them to carry them over into my KSH script. Is there a way to do this?

and Tom said...

the easiest way I know to do this is goes like this:




#!/bin/ksh

cat <<EOF > /tmp/$$.sql
set termout off
set feedback off
set echo off
spool /tmp/$$.ksh
begin
for x in ( select rownum, username from all_users where rownum <= 5 )
loop
dbms_output.put_line( 'user' || x.rownum || '=' || x.username );
end loop;
end;
/
exit
EOF


spool off
sqlplus / @/tmp/$$.sql

. /tmp/$$.ksh

echo $user1
echo $user2
echo $user3
echo $user4
echo $user5

rm /tmp/$$.sql /tmp/$$.ksh

-----------------------------------------------------



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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 !

Tom Kyte
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.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library