Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Executing multiple Oracle procedures concurrently

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Executing multiple Oracle procedures concurrently
# 1  
Old 11-03-2005
Executing multiple Oracle procedures concurrently
I am using KSH with an OS of AIX Version 5.3.

From my shell script, that will be scheduled thorugh a CRON, I need to execute 2 Oracle stored procedures concurrently. If we execute them one at a time, the total execution time takes 4 hours or more. Since they are not dependent on each other and will never be processing the same records from the database, we would like to execute these 2 procedures concurrently.

Is there a way to do this in one shell script/CRON job or do I need 2 scripts/CRON jobs scheduled for the same start time?
# 2  
Old 11-03-2005
try:
Code:
nohup /path/to/sqlplus user/pass<<EOF &
<PL/SQL code here>
exit
EOF

/path/to/sqlplus /user/pass<<EOF 
<PL/SQL code here>
exit
EOF 
wait

# 3  
Old 11-04-2005
my PL/SQL code is in functions within the shell script and I am executing the functions in the main body of the script. How would I adapt your code to execute the 2 functions, jim mcnamara?

These are my functions:

#----------------------------------------------------------------------
# Function: execute_inst
# Description: Execute the stored procedure
#
# Called with: No arguments
# Returns: No arguments
#----------------------------------------------------------------------
execute_inst ()
{
sqlplus $ORACLE_UID/$ORACLE_PWD <<EOF
WHENEVER SQLERROR EXIT 1
DECLARE
ln_return_code NUMBER;
BEGIN
HSD_AUROMATED_SCRIPTS.SP_AUTOMATED_SCRIPTS_CTRL_INST(ln_return_code);
IF ln_return_code = -1 THEN
RAISE_APPLICATION_ERROR (-20001, 'Institutional Script Automation Process Terminated with Errors. Please check the logs for specific error');
END IF;
END;
/
EOF
return
}

#----------------------------------------------------------------------
# Function: execute_prof
# Description: Execute the stored procedure
#
# Called with: No arguments
# Returns: No arguments
#----------------------------------------------------------------------
execute_prof ()
{
sqlplus $ORACLE_UID/$ORACLE_PWD <<EOF
WHENEVER SQLERROR EXIT 1
DECLARE
ln_return_code NUMBER;
BEGIN
HSD_AUROMATED_SCRIPTS.SP_AUTOMATED_SCRIPTS_CTRL_PROF(ln_return_code);
IF ln_return_code = -1 THEN
RAISE_APPLICATION_ERROR (-20001, 'Professional Script Automation Process Terminated with Errors. Please check the logs for specific error');
END IF;
END;
/
EOF
return
}


and this is how I am calling them right now:

stty echo
clear

execute_inst
RC1=$?

execute_prof
RC21=$?

if [ $RC1 -ne 0 ]
then
echo " \n Error occured ...."
echo " \n INstitutional process failed ...."
exit 1
else
echo " \n INstitutional process Completed with return code 0"
exit 0
fi

if [ $RC2 -ne 0 ]
then
echo " \n Error occured ...."
echo " \n Professional process failed ...."
exit 1
else
echo " \n Professional process Completed with return code 0"
exit 0
fi
# 4  
Old 11-04-2005
try something like this -

Code:
stty echo
clear

#----------------------------------------------------------------------
# Function: execute_inst
# Description: Execute the stored procedure
#
# Called with: No arguments
# Returns: No arguments
#----------------------------------------------------------------------
RC1=${nohup sqlplus $ORACLE_UID/$ORACLE_PWD <<EOF 
WHENEVER SQLERROR EXIT 1
DECLARE
ln_return_code NUMBER;
BEGIN
HSD_AUROMATED_SCRIPTS.SP_AUTOMATED_SCRIPTS_CTRL_INST(ln_return_code);
IF ln_return_code = -1 THEN
RAISE_APPLICATION_ERROR (-20001, 'Institutional Script Automation Process Terminated with Errors. Please check the logs for specific error');
END IF;
END;
/
EOF} &

#----------------------------------------------------------------------
# Function: execute_prof
# Description: Execute the stored procedure
#
# Called with: No arguments
# Returns: No arguments
#----------------------------------------------------------------------
RC2=${nohup sqlplus $ORACLE_UID/$ORACLE_PWD <<EOF 
WHENEVER SQLERROR EXIT 1
DECLARE
ln_return_code NUMBER;
BEGIN
HSD_AUROMATED_SCRIPTS.SP_AUTOMATED_SCRIPTS_CTRL_PROF(ln_return_code);
IF ln_return_code = -1 THEN
RAISE_APPLICATION_ERROR (-20001, 'Professional Script Automation Process Terminated with Errors. Please check the logs for specific error');
END IF;
END;
/
EOF} 

wait

retval=0
if [ $RC1 -ne 0 ] 
then
   echo " \n Error occurred ...."
   echo " \n INstitutional process failed ...."
   retval=1
else
   echo " \n INstitutional process Completed with return code 0"
fi

if [ $RC2 -ne 0 ] 
then
echo " \n Error occurred ...."
echo " \n Professional process failed ...."
retval=1
else
echo " \n Professional process Completed with return code 0"
fi
exit $retval

# 5  
Old 11-04-2005
OK, call me dense, but I'm still having a problem understanding how to put that code in my script. Here is my entire script:

#!/bin/ksh
#======================================================================
#
# Script: mon_auto_process.sh
# Author:
# Date: June 07, 2005
#
# Description: This script executes the stored procedure, from the
# the scheulder (CTRL+M), that is responsible for
# executing the various Monday scripts that do not
# require manual intervention. Successful completion
# will enable the finance cycle to run without aborting.
#
# Modifications:
#
#======================================================================
# command line parameters
#
# $1 stewardship id
# $2 subproject
# $3 project
# $4 stage
#
#======================================================================

trap "stty echo ; exit 1" 1 2 15

#----------------------------------------------------------------------
# Function: execute_inst
# Description: Execute the stored procedure
#
# Called with: No arguments
# Returns: No arguments
#----------------------------------------------------------------------
execute_inst ()
{
sqlplus $ORACLE_UID/$ORACLE_PWD <<EOF
WHENEVER SQLERROR EXIT 1
DECLARE
ln_return_code NUMBER;
BEGIN
HSD_AUROMATED_SCRIPTS.SP_AUTOMATED_SCRIPTS_CTRL_INST(ln_return_code);
IF ln_return_code = -1 THEN
RAISE_APPLICATION_ERROR (-20001, 'Institutional Script Automation Process Terminated with Errors. Please check the logs for specific error');
END IF;
END;
/
EOF
return
}

#----------------------------------------------------------------------
# Function: execute_prof
# Description: Execute the stored procedure
#
# Called with: No arguments
# Returns: No arguments
#----------------------------------------------------------------------
execute_prof ()
{
sqlplus $ORACLE_UID/$ORACLE_PWD <<EOF
WHENEVER SQLERROR EXIT 1
DECLARE
ln_return_code NUMBER;
BEGIN
HSD_AUROMATED_SCRIPTS.SP_AUTOMATED_SCRIPTS_CTRL_PROF(ln_return_code);
IF ln_return_code = -1 THEN
RAISE_APPLICATION_ERROR (-20001, 'Professional Script Automation Process Terminated with Errors. Please check the logs for specific error');
END IF;
END;
/
EOF
return
}

#----------------------------------------------------------------------
#
# MAIN BODY
#
#----------------------------------------------------------------------

. /prod/ew/pvc/proj_admin/scripts/envparms -i $1 -s $2 -p $3 -e $4

unset ORACLE_PATH

#
# test oracle env
#
if [[ -z "${ORACLE_HOME}" ]]
then
print "${ME} error: ORACLE_HOME not set"
exit 1
fi
if [[ ! -d ${ORACLE_HOME} ]]
then
print "${ME} error: cannot find ${ORACLE_HOME}"
exit 1
fi

#
#
stty echo
clear

execute_inst
RC1=$?

execute_prof
RC21=$?

if [ $RC1 -ne 0 ]
then
echo " \n Error occured ...."
echo " \n INstitutional process failed ...."
exit 1
else
echo " \n INstitutional process Completed with return code 0"
exit 0
fi

if [ $RC2 -ne 0 ]
then
echo " \n Error occured ...."
echo " \n Professional process failed ...."
exit 1
else
echo " \n Professional process Completed with return code 0"
exit 0
fi
# 6  
Old 11-04-2005
Basically I rewrote the whole script - look at it as a replacement, not a code snippet that you insert somewhere.

You definitely need to test it first.
# 7  
Old 11-04-2005
I ran the script and it is giving me an error. Here is what the total script looks like right now:
#!/bin/ksh
#======================================================================
#
# Script: mon_auto_process.sh
# Author:
# Date: June 07, 2005
#
# Description: This script executes the stored procedure, from the
# the scheulder (CTRL+M), that is responsible for
# executing the various Monday scripts that do not
# require manual intervention. Successful completion
# will enable the finance cycle to run without aborting.
#
# Modifications:
#
#======================================================================
#----------------------------------------------------------------------
# Function: execute_inst
# Description: Execute the stored procedure
#
# Called with: No arguments
# Returns: No arguments
#----------------------------------------------------------------------
RC1=${nohup sqlplus $ORACLE_UID/$ORACLE_PWD <<EOF
WHENEVER SQLERROR EXIT 1
DECLARE
ln_return_code NUMBER;
BEGIN
HSD_AUTOMATED_SCRIPTS.SP_AUTOMATED_SCRIPTS_CTRL_INST(ln_return_code);
IF ln_return_code = -1 THEN
RAISE_APPLICATION_ERROR (-20001, 'Institutional Script Automation Process Terminated with Errors. Please check the logs for specific error');
END IF;
END;
/
EOF} &

#----------------------------------------------------------------------
# Function: execute_prof
# Description: Execute the stored procedure
#
# Called with: No arguments
# Returns: No arguments
#----------------------------------------------------------------------
RC2=${nohup sqlplus $ORACLE_UID/$ORACLE_PWD <<EOF
WHENEVER SQLERROR EXIT 1
DECLARE
ln_return_code NUMBER;
BEGIN
HSD_AUTOMATED_SCRIPTS.SP_AUTOMATED_SCRIPTS_CTRL_PROF(ln_return_code);
IF ln_return_code = -1 THEN
RAISE_APPLICATION_ERROR (-20001, 'Professional Script Automation Process Terminated with Errors. Please check the logs for specific error');
END IF;
END;
/
EOF}

wait

retval=0
if [ $RC1 -ne 0 ]
then
echo " \n Error occurred ...."
echo " \n INstitutional process failed ...."
retval=1
else
echo " \n INstitutional process Completed with return code 0"
fi

if [ $RC2 -ne 0 ]
then
echo " \n Error occurred ...."
echo " \n Professional process failed ...."
retval=1
else
echo " \n Professional process Completed with return code 0"
fi
exit $retval

here is how I run it:
mon_auto_process2.sh

and here is my output:
mon_auto_process2.sh[24]: syntax error at line 24 : `<' unexpected
Login or Register to Ask a Question

Previous Thread | Next Thread

8 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Run multiple procedures from shell script parallely

Hi, I need to write a Shell Script wherein i will connect to a DB2 Database and run multiple DB procedures. I know how to do in a way where procedures will be called one after the other, like when first procedure finishes, second will be executed. But what i want is to run them at the same time... (11 Replies)
Discussion started by: Neelkanth
11 Replies

2. Shell Programming and Scripting

Executing oracle procedure using cronjob

Hi, Below is the code to execute the procedure "dbms_job.broken" from the shell script. on executing manually, it works properly without any error. but it is not working when scheduled using the cronjob. #!/usr/bin/bash user_name="oracdb" password="ora123" tns="localdb"... (2 Replies)
Discussion started by: milink
2 Replies

3. Solaris

Help with executing multiple remote commands after multiple hops

Hi SSHers, I have embedded this below code in my shell script.. /usr/bin/ssh -t $USER@$SERVER1 /usr/bin/ssh $USER2@S$SERVER2 echo uptime:`/opt/OV/bin/snmpget -r 0 -t 60 $nodeName system.3.0 | cut -d: -f3-5` SSH to both these servers are public-key authenticated, so things run... (13 Replies)
Discussion started by: LinuxUser2008
13 Replies

4. Shell Programming and Scripting

Executing a Oracle SQL statement in a UNIX script

Hi All, I need to select one column from a table based upon the passed in parameter. I tried this: sqlplus -silent $MISP_USER << EOF set feedback off; set verify off; set sqlprompt "" SELECT mail_flag FROM dailyjobs WHERE job_name = '$1'; exit 0 EOF exit... (1 Reply)
Discussion started by: ganga.dharan
1 Replies

5. Shell Programming and Scripting

Oracle procedure is not executing in uix

Hi Guys, I am trying to tun a oracle proedure throgh unix shell script but it is not running i dont know why ? i have tested this procedure in sqlplus and it was working fine. can you see the script and sql file and let me know where is my mistake. script:bm_chart_table_loading.sh ... (3 Replies)
Discussion started by: shary
3 Replies

6. Shell Programming and Scripting

Running same script multiple times concurrently...

Hi, I was hoping someone would be able to help me out. I've got a Python script that I need to run 60 times concurrently (with the number added as an argument each time) via nightly cron. I figured that this would work: 30 1 * * * for i in $(seq 0 59); do $i \&; done However, it seems to... (4 Replies)
Discussion started by: ckhowe
4 Replies

7. Solaris

Executing MS-SQL stored procedures from Unix/C Program?

All, We are contemplating a port of an existing software product and would like to expend as little effort as possible. Our new database would be MS-SQL, and we would write stored procedures to perform common db operations. We'd like to call these stored procedures from C or C++ code running... (3 Replies)
Discussion started by: mparks
3 Replies

8. Solaris

Calling Oracle Stored Procedures in UNIx(sun solaris)

I have created 3 Procedures all similar to this one: I then created 3 shell sripts which will call the sql? finally created a calling script to call the procedure. I am a bit unsure how to this all works, can someone check my code and I am doing this right? Also could I add my procedure (first... (0 Replies)
Discussion started by: etravels
0 Replies
Login or Register to Ask a Question