Executing multiple Oracle procedures concurrently Post: 88461

Sponsored Content

Top Forums Shell Programming and Scripting Executing multiple Oracle procedures concurrently Post 88461 by multidogzoomom on Friday 4th of November 2005 09:19:31 AM

11-04-2005

Registered User

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

multidogzoomom

View Public Profile for multidogzoomom

Find all posts by multidogzoomom

8 More Discussions You Might Find Interesting

1. 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...

2. 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. 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. 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 ...

5. 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...

6. 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...

7. 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"...

8. 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...

LEARN ABOUT DEBIAN

sql::reservedwords::oracle

SQL::ReservedWords::Oracle(3pm) 			User Contributed Perl Documentation			   SQL::ReservedWords::Oracle(3pm)

NAME

       SQL::ReservedWords::Oracle - Reserved SQL words by Oracle

SYNOPSIS

	  if ( SQL::ReservedWords::Oracle->is_reserved( $word ) ) {
	      print "$word is a reserved Oracle word!";
	  }

DESCRIPTION

       Determine if words are reserved by Oracle Database.

METHODS

       is_reserved( $word )
	   Returns a boolean indicating if $word is reserved by either Oracle7, Oracle8i, Oracle9i or Oracle10g.

       is_reserved_by_oracle7( $word )
	   Returns a boolean indicating if $word is reserved by Oracle7.

       is_reserved_by_oracle8( $word )
	   Returns a boolean indicating if $word is reserved by Oracle8i.

       is_reserved_by_oracle9( $word )
	   Returns a boolean indicating if $word is reserved by Oracle9i.

       is_reserved_by_oracle10( $word )
	   Returns a boolean indicating if $word is reserved by Oracle10g.

       reserved_by( $word )
	   Returns a list with Oracle versions that reserves $word.

       words
	   Returns a list with all reserved words.

EXPORTS

       Nothing by default. Following subroutines can be exported:

       is_reserved
       is_reserved_by_oracle7
       is_reserved_by_oracle8
       is_reserved_by_oracle9
       is_reserved_by_oracle10
       reserved_by
       words

SEE ALSO

       SQL::ReservedWords

       <http://www.oracle.com/technology/documentation/>

AUTHOR

       Christian Hansen "chansen@cpan.org"

COPYRIGHT

       This program is free software, you can redistribute it and/or modify it under the same terms as Perl itself.

perl v5.8.8							    2008-03-28					   SQL::ReservedWords::Oracle(3pm)