|
|
| Re: Calling stored procedures from unix scripts [message #97448 is a reply to message #97447] |
Mon, 13 May 2002 11:39  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
You can play with this code... Just change the ~~ to two '<' to make it work.
#!/bin/ksh
## CREATE OR REPLACE PROCEDURE p1 (i_parm IN NUMBER, o_parm OUT NUMBER)
## IS
## BEGIN
## o_parm := 5 * i_parm;
## END;
## /
my_in_parm=5
echo =============================
echo FIRST
echo =============================
RETVAL=`sqlplus -s scott/tiger@dev ~~EOF
set serveroutput on
WHENEVER SQLERROR EXIT 1
declare
x number := $my_in_parm;
y number;
begin
p1(x, y);
dbms_output.put_line('o_parm from p1 is '||y);
end;
/
exit;
EOF`
echo $RETVAL
X=`echo $RETVAL | awk '{print $5}'`
echo $X
## CREATE OR REPLACE PROCEDURE p2 (i_parm IN NUMBER, o_parm OUT NUMBER)
## IS
## BEGIN
## o_parm := 5 * i_parm;
## DBMS_OUTPUT.put_line ('Line one');
## DBMS_OUTPUT.put_line ('Line two');
## END;
## /
my_in_parm=5
echo =============================
echo SECOND
echo =============================
set -A my_arr `sqlplus -s scott/tiger@dev ~~EOF
set serveroutput on
WHENEVER SQLERROR EXIT 1
declare
x number := $my_in_parm;
y number;
begin
p2(x, y);
dbms_output.put_line('o_parm from p1 is '||y);
end;
/
exit;
EOF`
echo "there are ${#my_arr[[*]]} elements in the array"
element=0
while [[ $element -lt ${#my_arr[[*]]} ]]
do
echo "==>"${my_arr[[$element]]}
let element=$element+1;
done
echo "Echo all in one command now!"
echo ${my_arr[[*]]}
>t.ksh
=============================
FIRST
=============================
o_parm from p1 is 25 PL/SQL procedure successfully completed.
25
=============================
SECOND
=============================
there are 13 elements in the array
==>Line
==>one
==>Line
==>two
==>o_parm
==>from
==>p1
==>is
==>25
==>PL/SQL
==>procedure
==>successfully
==>completed.
Echo all in one command now!
Line one Line two o_parm from p1 is 25 PL/SQL procedure successfully completed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
