lucifer lucifer - 1 month ago 20
Bash Question

how to call a oracle procedure from shell with parameter

I have a procedure which adds two numbers.I want to call that procedure from shell.i can call procedure without argument.For ex

create or replace procedure printTheName
is
begin
dbms_output.put_line('This is a procedure'):
end;
/


this is the procedure which prints a message. i can call it from shell using this

#!/bin/sh
sqlplus -s system/oracle10g@orcl<<END
execute printTheName();
commit;


This is running fine Now i have a procedure which adds two numbers i have to call that from shell this is the procedure.

declare
a number(2);
b number(2);
c number(2);
begin
a:=&a;
b:=&b;
c:=a+b;
dbms_output.put_line(a|| ' + '||b||' = '||c);
end;

Answer

Use pipe to pass commands to sqlplus

echo "execute printTheName(10,10);
commit;
execute procedure2();
" | sqlplus system/oracle10g@orcl

note that you cant put as many sqlplus commands as you want inside the double quotes.