Home » Infrastructure » Unix » Calling stored procedure from shell
Calling stored procedure from shell [message #97335] Sat, 16 March 2002 01:42 Go to next message
Oswaldo Castro
Messages: 2
Registered: March 2002
Junior Member
Hi List

I'm trying to call a stored procedure inside a bash script passing it a parameter. I have the following code: (it must be run by root because of other commands on script)

#./run_proc 15032002

#!/bin/bash
# the run_proc script
PROC_DATE=$1
export PROC_DATE
su - oracle -c "PROC_DATE=$PROC_DATE sqlplus /nolog @exec_procedure.sql"

Inside @exec_procedure I have:

connect user/password@inst
spool file_${PROC_DATE}.log # here it works...
execute myprocedure ($PROC_DATE) # here it doesn't
exit

On the spool clause the substitution occurs and I got a file named "file_15032002.log", but the execute clause does not get substituted. The problem is that I don't know how to pass the argument to the stored procedure. The opened sql session try to interpret the $PROC_DATE inside its parsing step and gave me an error.

I really need help on this. I will be very glad for any kind of information

Thanks in advance

Oswaldo Castro
Re: Calling stored procedure from shell [message #97358 is a reply to message #97335] Mon, 25 March 2002 00:17 Go to previous messageGo to next message
MOE
Messages: 14
Registered: February 2002
Junior Member
Hi,
try to use 'here-documents':

#!/bin/bash
var1='test'
PROC_DATE="'2002-03-22'"
sqlplus user/passwd@service << END
-- here you are inside sqlplus, but you have all the benefits of the shell variables
prompt $var1
exec myprocedure (to_date ($PROC_DATE, 'YYYY-MM-DD'));
-- you don't need EXIT or QUIT
-- the the label of the here-document closes sqlplus
END

MOe
Re: Calling stored procedure from shell [message #98057 is a reply to message #97358] Thu, 08 January 2004 22:05 Go to previous messageGo to next message
venkatesh
Messages: 72
Registered: August 2000
Member
wht if the shell script takes more than 9 parameters??
how do i access the 10th parameters thru shell script???
Re: Calling stored procedure from shell [message #98401 is a reply to message #97358] Mon, 13 September 2004 12:49 Go to previous messageGo to next message
Balaji
Messages: 102
Registered: October 2000
Senior Member
I have a stored procedure with out parameter. How do I pass a variable from shell script to that procedure and get the results back?
Re: Calling stored procedure from shell [message #192320 is a reply to message #98401] Mon, 11 September 2006 18:03 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Reported By: aasomani On: Mon, 11 September 2006 23:35

No one has answered it yet and I am also stuck with same problem. I have three OUT parameters which has some value in it, and I have to capture those values in to my shell variables. How to do this waiting for reply.........


Report a post only if it is violating something/not appropriate.
Reporting a post to get attention will actually work the other way.
Re: Calling stored procedure from shell [message #192554 is a reply to message #192320] Tue, 12 September 2006 19:19 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
#!/bin/ksh
## CREATE OR REPLACE PROCEDURE p1 (i_parm IN NUMBER, o_parm1 OUT NUMBER, o_parm2 OUT NUMBER)
## IS
## BEGIN
##    o_parm1 := 5 * i_parm;
##    o_parm2 := 10 * i_parm;
## END;
## /
my_in_parm=5
RETVAL=`sqlplus -s scott/tiger@dev <<EOF
set serveroutput on
declare
x number := $my_in_parm;
y number;
z number;
begin
  p1(x, y, z);
  dbms_output.put_line('KeepThis '|| y ||' '|| z);
end;
/
exit;
EOF`

Y=`echo $RETVAL | grep KeepThis | awk '{print $2}'`
Z=`echo $RETVAL | grep KeepThis | awk '{print $3}'`
echo o_parm1=$Y
echo o_parm2=$Z

The output...
>tt.ksh   
o_parm1=25
o_parm2=50

Re: Calling stored procedure from shell [message #192555 is a reply to message #192554] Tue, 12 September 2006 19:23 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
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

Previous Topic: Oracle 8i and Solaris 10
Next Topic: Installation on SPARC Solaris 9
Goto Forum:
  


Current Time: Wed Dec 07 10:54:03 CST 2016

Total time taken to generate the page: 0.09069 seconds