Home » Infrastructure » Unix » how to call stored procedure in shell script
how to call stored procedure in shell script [message #612871] Sat, 26 April 2014 13:25 Go to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
how to call stored procedure multiple times in shell script

what i tried

for count in 1 2 3 4 5 6 7 8
do
sp_stored prcedure
done


when i execute this i get this error ' is not expected. at line 3 : `do
Re: how to call stored procedure in shell script [message #612872 is a reply to message #612871] Sat, 26 April 2014 13:41 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What stored procedure in shell?
How is this related to Oracle?

Re: how to call stored procedure in shell script [message #612873 is a reply to message #612872] Sat, 26 April 2014 13:46 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
im writing a shell script to call a stored procedure
Re: how to call stored procedure in shell script [message #612875 is a reply to message #612873] Sat, 26 April 2014 14:08 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A stored procedure? What kind a stored procedure?
Explain.

Re: how to call stored procedure in shell script [message #612890 is a reply to message #612875] Sun, 27 April 2014 08:06 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
ok,something like this

i want to run the sp_insert_customer_parent to run eight times so i put that in a loop,but when i add the for loop down there the scripts doesnt work

export PATH=$ORACLE_HOME/bin:$PATH
ORACLE_SID=xxxx; export ORACLE_SID ORAENV_ASK=NO
TODAY_DATE=$(date +"%Y%m%d")
LOGFILE=/var/opt/gogd/cust/scripts/${TODAY_DATE}_sap_cust_rel.log
echo 'Connected'
PARCUST=sap_cust_rel.par
sqlldr parfile=${PARCUST} >> $LOGFILE
echo 'started' 
v_out_str=`sqlplus -S xxxxx/xxxxx << EOF
set serveroutput on 
set feedback on
declare 
l_err_msg varchar2(1000);
begin
sp_control_customer_insert('E');
sp_update_customer();
dbms_output.put_line('customer update');
for COUNTER in  1 2 3 4 5 6 7 8
do
sp_insert_customer_parent();
done
dbms_output.put_line('inserted missing parent');
sp_cust_hier_dnorm_2();
dbms_output.put_line('table denormalised');
sp_control_customer_insert('D');
exception
when others then 
l_err_msg:=sqlerrm;
dbms_output.put_line('Sql error '|| l_err_msg);
end;
/
set serveroutput off
set feedback off
set verify off
set heading off
exit;
EOF`
Re: how to call stored procedure in shell script [message #612894 is a reply to message #612890] Sun, 27 April 2014 08:27 Go to previous messageGo to next message
Michel Cadot
Messages: 60012
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please INDENT your code, this is unreadable.
So you want to do a loop in PL/SQL not a shell loop, doesn't it?

exception
when others then 
l_err_msg:=sqlerrm;
dbms_output.put_line('Sql error '|| l_err_msg);


This is very bad, remove it.

[Updated on: Sun, 27 April 2014 10:47]

Report message to a moderator

Re: how to call stored procedure in shell script [message #612896 is a reply to message #612894] Sun, 27 April 2014 08:35 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
i want the particular sp to run multiple times how can i do that .im using a bourne shell

[Updated on: Sun, 27 April 2014 09:05]

Report message to a moderator

Re: how to call stored procedure in shell script [message #612897 is a reply to message #612896] Sun, 27 April 2014 09:08 Go to previous messageGo to next message
BlackSwan
Messages: 23158
Registered: January 2009
Senior Member
what problem are you really trying to solve?
Re: how to call stored procedure in shell script [message #612899 is a reply to message #612897] Sun, 27 April 2014 09:12 Go to previous messageGo to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
i would like to know how a stored procedure could be called multiple times from shell script?this is my question
Re: how to call stored procedure in shell script [message #612900 is a reply to message #612899] Sun, 27 April 2014 09:19 Go to previous messageGo to next message
BlackSwan
Messages: 23158
Registered: January 2009
Senior Member
sp_insert_customer_parent();
sp_insert_customer_parent();
sp_insert_customer_parent();
sp_insert_customer_parent();
sp_insert_customer_parent();
Re: how to call stored procedure in shell script [message #612901 is a reply to message #612900] Sun, 27 April 2014 09:23 Go to previous message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
thanks micheal for pointing oit out,found what was causing the trouble

[Updated on: Sun, 27 April 2014 10:43]

Report message to a moderator

Previous Topic: how to install bash 3.0
Next Topic: oracle_home path set up help
Goto Forum:
  


Current Time: Sun Dec 21 17:20:24 CST 2014

Total time taken to generate the page: 0.05682 seconds