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?
try:
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
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
try something like this -
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
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
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.
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