How to return result of an Oracle stored procedure to a shell script -
i have oracle procedure proc1 adds 2 values , gives result.i have call procedure shell , show result shell.i able call procedure shell,but shows pl/sql procedure completed.but result not coming shell .
i doing call procedure shell...
$ echo "execute proc1(10,10);"|sqlplus -s system/xxxxx@orcl
this procedure running fine .
create or replace procedure proc1 (n1 in number,n2 in number) begin dbms_output.put_line(n1+n2); end; /
i need output in shell .anyone plese help.
i aware there answer showing how use set serveroutput on
, procedure call on separate lines, writing answer one-liner same thing.
bascially need shove sqlplus:
set serveroutput on execute proc1(10,10);
you might @ first think can done on 1 line, separated semi-colon.
set serveroutput on; execute proc1(10,10);
however doesn't work - need newline character.
so trick use -e
flag echo
, can give newline \n
.
using head -1
trims everyting line containing procedure result.
final one-line answer:
echo -e "set serveroutput on\n execute proc1(10,10);"|sqlplus -s system/xxxxx@orcl| head -1
p.s. editted question remove password :)
Comments
Post a Comment