how to pass the values to unix shell from the oracle stored procedure.

Shell Programming and Scripting
1

Hi

i am calling a stored procedure from unix shell like this call

test_proc('0002','20100218');

the stored procedure was giving output

like this dbms_output.put_line(' processed earlier');

i want to see the output in the unix shell where i called.

Thanks
barani

2

Try:

var=`sqlplus -s user/pass@conn_string <<EOF
set feedback off
execute test_proc('0002','20100218');
exit;
EOF
`
echo $var
3

hi its not working.

4

Are you getting any error? What is the error? Have you checked your s.proc? Is it returing values?

5

That's funny.
It's like saying "My car won't start. What's the problem?"
There could be gazillion different problems. You'll have to give us the details so we can help you. Help us help you.
Otherwise we can only make wild guesses. Or maybe a few could gaze at the crystal ball and somehow read your mind.

Btw, hope you've used "sqlplus -s user/password@conn_string <<EOF" in the script posted earlier.

tyler_durden

6

consider this my stored procedure

create procedure proc(name IN varchar, age IN varchar) 
as 
var varchar;

begin

select * into var from table where colname='name'

if sqlorwcount =0

dbms_output.put.line('name doesnot exit' );

end;

from unix i am calling the procedure like this

call proc('abc',''ten);

i could not able to find the output after executing the unix script.

Thanks

7

If you are expecting output from a dbms_output.put.line command, make sure you have

SET SERVEROUT ON

amongst your sqlplus commands.