Sponsored Content
Top Forums Shell Programming and Scripting How to call a stored procedure inside a loop? Post 302899131 by ashwanth on Sunday 27th of April 2014 10:09:26 AM
Old 04-27-2014
How to call a stored procedure inside a loop?

How to call a stored procedure inside a loop

Code:
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`


Last edited by Scrutinizer; 04-27-2014 at 11:44 AM.. Reason: CODE tags
 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Stored Procedure on NT/SQLServer

Hi: How will I execute a Stored Procedure that sits on NT/SQLServer. Any help would be appreciated. Thanks (3 Replies)
Discussion started by: mayohan
3 Replies

2. Shell Programming and Scripting

Need to call stored procedure from unix script

Hi Guys, I have a stored procedure which has 5 out parameters. I need to call the stored procedure from the script. When i use the following in my script, db2 "CALL FCFM.PART_MASTER_TMP($Return_code,$Message,$Message1,$SQL,$Load_count)" >> $LOG_FILE I am getting an error.. Please... (1 Reply)
Discussion started by: mac4rfree
1 Replies

3. Shell Programming and Scripting

need to call 3 stored procedure at the same time

Hi GUYS, I need to trigger 3 stored procedure at the same time.. I know how to trigger the stored procedure. If anybody can tell me how to trigger 3 different process at the same time parallelly.. that would be helpful.. Thanks for your help in advance, Magesh (1 Reply)
Discussion started by: mac4rfree
1 Replies

4. Programming

How to call sqlloader from stored procedure!!! Advise

Hi , I have a dellimited .dat file and a sqlloader. I want to call the sqlloader from a oracle stored procedure. The procedure should return the result of sqlloader's execution. (3 Replies)
Discussion started by: Haque123
3 Replies

5. Shell Programming and Scripting

how to call oracle stored procedure from unix shell

Hi i want to call a oracle stored procedure from unix (using bash shell). consider this is my oracle stored procedure with parameter create procedure testproc(name IN varchar, age IN Number, id OUT Number ) AS begin id=1; dbms_output.put.line('successfull validation') end;... (6 Replies)
Discussion started by: barani75
6 Replies

6. Shell Programming and Scripting

Call and redirect output of Oracle stored procedure from unix script

Hi, Can you assist me in how to redirect the output of oracle stored procedure from unix script? Something similar to what i did for sybase isql -U$MYDBLOG -D$MYDBNAME -S$MYDBSVR -P$MYDBPWD -o$MYFILE<< %% proc_my_test 8 go %% Thanks in advance - jak (0 Replies)
Discussion started by: jakSun8
0 Replies

7. Shell Programming and Scripting

How to create and call mysql stored procedure in perl?

Hi, I want to create MySQL stored procedure and call the stored procedure using perl. I tried like this: use DBI; my $dbh = DBI->connect ("DBI:mysql:test", "root", "ibab", { RaiseError => 1, PrintError => 0}); $create_procedure =... (5 Replies)
Discussion started by: vanitham
5 Replies

8. Shell Programming and Scripting

How to call a stored procedure from shell program?

How to call a stored procedure from shell program (1 Reply)
Discussion started by: noorm
1 Replies

9. Shell Programming and Scripting

How to call stored procedure with CLOB out parameter from shell script?

I have written a stored procedure in oracle database, which is having a CLOB OUT parameter. How can i call this stored procedure from shell script and get the CLOB object in shell script variable? (0 Replies)
Discussion started by: vel4ever
0 Replies

10. Shell Programming and Scripting

Stored Procedure not executing

below code is not executing the stored procedure,not sure what the issue.Even sqllog is blank.please help me its very urgent. sqlplus -s $connect_str@$DB_ORACLE_SID >> ${SQL_LOG_FILE} << EOF set serverout on set feed off set head off set pages 0 set colsep , set tab off set lin 150... (3 Replies)
Discussion started by: katakamvivek
3 Replies
continue(1T)						       Tcl Built-In Commands						      continue(1T)

__________________________________________________________________________________________________________________________________________________

NAME
continue - Skip to the next iteration of a loop SYNOPSIS
continue _________________________________________________________________ DESCRIPTION
This command is typically invoked inside the body of a looping command such as for or foreach or while. It returns a TCL_CONTINUE code, which causes a continue exception to occur. The exception causes the current script to be aborted out to the innermost containing loop command, which then continues with the next iteration of the loop. Catch exceptions are also handled in a few other situations, such as the catch command and the outermost scripts of procedure bodies. EXAMPLE
Print a line for each of the integers from 0 to 10 except 5: for {set x 0} {$x<10} {incr x} { if {$x == 5} { continue } puts "x is $x" } SEE ALSO
break(1T), for(1T), foreach(1T), return(1T), while(1T) KEYWORDS
continue, iteration, loop ATTRIBUTES
See attributes(5) for descriptions of the following attributes: +--------------------+-----------------+ | ATTRIBUTE TYPE | ATTRIBUTE VALUE | +--------------------+-----------------+ |Availability | SUNWTcl | +--------------------+-----------------+ |Interface Stability | Uncommitted | +--------------------+-----------------+ NOTES
Source for Tcl is available on http://opensolaris.org. Tcl continue(1T)
All times are GMT -4. The time now is 07:57 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy