Home » RDBMS Server » Server Administration » Calling procedure from Shell script
Calling procedure from Shell script [message #192352] Tue, 12 September 2006 00:46 Go to next message
gajini
Messages: 262
Registered: January 2006
Senior Member
Hi,

How to call a stored procedure from shell script and vice-versa?

Thanks,
Gajini
Re: Calling procedure from Shell script [message #192360 is a reply to message #192352] Tue, 12 September 2006 01:10 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
in your shell script call this sequence of statements.

sqlplus username/password@hoststring
execute procedure_name
exit



Regards,
Liza
Re: Calling procedure from Shell script [message #192375 is a reply to message #192352] Tue, 12 September 2006 01:38 Go to previous messageGo to next message
gajini
Messages: 262
Registered: January 2006
Senior Member
Thanks Liza,

It's working.One more query,how to call the shell script from stored procedure?
Re: Calling procedure from Shell script [message #192379 is a reply to message #192352] Tue, 12 September 2006 01:46 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
Please have a look:

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:16212348050

Regards
Re: Calling procedure from Shell script [message #192399 is a reply to message #192352] Tue, 12 September 2006 03:08 Go to previous messageGo to next message
gajini
Messages: 262
Registered: January 2006
Senior Member
Thanks.

Now i've to call the procedure PROC1 in which one parameter is IN & other
parameter is OUT type,how to use shell varaibles(say IN,OUT) to call this
procedure?
icon7.gif  Re: Calling procedure from Shell script [message #192423 is a reply to message #192352] Tue, 12 September 2006 05:10 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
It took a qiute long time, but i am happy that finally i got a solution for you. Smile

The Procedure:
Create or replace procedure in_out_test(p1 in number,p2 out number)
is
begin
   p2:= p1+1;
end;

Shell script:
in_value=2
var=`sqlplus -s username/password <<EOF @in_out.sql $in_value EOF`
echo $var

Remember shell variable in_value is used to pass the value in PL/SQL.
in_out.sql
set serveroutput on
set feedback off
variable out_test number
exec in_out_test(&1,:out_test)
exec dbms_output.put_line(:out_test)
exit;
Re: Calling procedure from Shell script [message #192515 is a reply to message #192423] Tue, 12 September 2006 12:58 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
#!/bin/ksh
RETVAL=`sqlplus -s scott/pass@dev <<EOF
set serveroutput on
WHENEVER SQLERROR EXIT 1
declare
x number;
begin
x := 999;
dbms_output.put_line('the_result_is '||x);
end;
/
exit;
EOF`

echo $RETVAL
X=`echo $RETVAL | awk '{print $2}'`
echo $X

sqlplus -s scott/pass@dev <<EOF | read RETVAL
set serveroutput on
WHENEVER SQLERROR EXIT 1
declare
x number;
begin
x := 999;
dbms_output.put_line('the_result_is '||x);
end;
/
exit;
EOF

echo $RETVAL

X=`echo $RETVAL | awk '{print $2}'`
echo $X

Output:
-------
>t.ksh
the_result_is 999 PL/SQL procedure successfully completed.
999
the_result_is 999
999




------------------------------------------------------
#!/bin/ksh
hostvar=5
RETVAL=`sqlplus -s scott/tiger@dev <<EOF
set serveroutput on
declare
x number := $hostvar;
begin
execute immediate 'begin dbms_output.put_line(''bind variable=''||:bindvar); end;' using x;
end;
/
exit;
EOF`
echo $RETVAL


----

>t.ksh    
bind variable=5 PL/SQL procedure successfully completed.
------------------------------------------------------


#!/bin/ksh
#======================================================
# stored proc with parms
#======================================================
## 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.



--============= 
Query and dbms_ouput together in one script
--============= 

#!/bin/ksh
sqlplus -s scott/tiger@engdev <<EOF > tmp.txt
set pagesize 0 feedback off verify off heading off echo off
set serveroutput on
--select name from v\$parameter where name like 'nls%' and rownum <6;
select table_name from user_tables where rownum <6;
begin
  dbms_output.put_line('output parm#1');
  dbms_output.put_line('output parm#2');
end;
/
exit;
EOF

while read reslt_line
do
  echo "==>"$reslt_line
done < tmp.txt

==>BONUS
==>CUSTOMER
==>DATE_TAB
==>DEPT
==>DUMMY
==>output parm#1
==>output parm#2
Re: Calling procedure from Shell script [message #192862 is a reply to message #192515] Thu, 14 September 2006 00:09 Go to previous message
gajini
Messages: 262
Registered: January 2006
Senior Member
Thanks Liza & Andrew for helping me to understand it well.
Previous Topic: increase sessions and processes in oracle 9.2.0
Next Topic: Connect to another server and execute file
Goto Forum:
  


Current Time: Thu Mar 28 05:14:10 CDT 2024