Home » SQL & PL/SQL » SQL & PL/SQL » Main Stored procedure to execute multiple procedures (Oracle 11.2,Linux )
Main Stored procedure to execute multiple procedures [message #669343] Mon, 16 April 2018 11:44 Go to next message
senmng
Messages: 4
Registered: April 2018
Junior Member
Hi - I have a requirement to design a stored procedure which can call multiple procedures and run in an sequential order and it should get it stopped if any of the previous proc gets failed.

Also,i want to capture and store the exception handling in an error table. Here is the initial version of the stored procedure.
 CREATE PROC MainProc
 AS
 DECLARE @res int
 exec @res=p1
 IF @res<>0
     RETURN
 ELSE
     PRINT 'P1 executed successfully'
 exec @res=p2
 IF @res<>0
     RETURN
 ELSE
     PRINT 'P2 executed successfully'
 exec @res=p3
 IF @res<>0
     RETURN
 ELSE
     PRINT 'P3 executed successfully'
 exec @res=p4 
 IF @res<>0
     RETURN
 ELSE
     PRINT 'P4 executed successfully'
 exec @res=p5
 IF @res<>0
     RETURN
 ELSE
     PRINT 'P5 executed successfully'
Thanks,
Sen
*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read

[Updated on: Mon, 16 April 2018 12:02] by Moderator

Report message to a moderator

Re: Main Stored procedure to execute multiple procedures [message #669346 is a reply to message #669343] Mon, 16 April 2018 12:03 Go to previous messageGo to next message
BlackSwan
Messages: 25963
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

please post full results from SQL below


SELECT * FROM V$VERSION;
Re: Main Stored procedure to execute multiple procedures [message #669347 is a reply to message #669346] Mon, 16 April 2018 12:40 Go to previous messageGo to next message
senmng
Messages: 4
Registered: April 2018
Junior Member
Hi - Here is the query result


Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Re: Main Stored procedure to execute multiple procedures [message #669348 is a reply to message #669343] Mon, 16 April 2018 12:44 Go to previous messageGo to next message
John Watson
Messages: 7364
Registered: January 2010
Location: Global Village
Senior Member
What you are trying to do is what Scheduler Job Chains are designed for. You design a chain, with branches after each step depending on success or failure. Built-in functionality from release 11.
Re: Main Stored procedure to execute multiple procedures [message #669353 is a reply to message #669348] Mon, 16 April 2018 14:09 Go to previous messageGo to next message
senmng
Messages: 4
Registered: April 2018
Junior Member
Here i need to capture the error in an error table.Can we accomplish it through Scheduler Job Chains? If,yes can you assist me in addressing it?
Re: Main Stored procedure to execute multiple procedures [message #669355 is a reply to message #669348] Mon, 16 April 2018 16:01 Go to previous messageGo to next message
senmng
Messages: 4
Registered: April 2018
Junior Member
Also we have some limitations in using oracle utilities and we are advised to use pretty straight forward oracle DBMS scheduler jobs not chain jobs.
Re: Main Stored procedure to execute multiple procedures [message #669357 is a reply to message #669353] Tue, 17 April 2018 00:37 Go to previous messageGo to next message
John Watson
Messages: 7364
Registered: January 2010
Location: Global Village
Senior Member
senmng wrote on Mon, 16 April 2018 20:09
Here i need to capture the error in an error table.Can we accomplish it through Scheduler Job Chains? If,yes can you assist me in addressing it?
Are you asking for consultancy services?
Re: Main Stored procedure to execute multiple procedures [message #669359 is a reply to message #669357] Tue, 17 April 2018 03:44 Go to previous messageGo to next message
cookiemonster
Messages: 13084
Registered: September 2008
Location: Rainy Manchester
Senior Member
You know the code you posted isn't even vaguely valid PL/SQL?
Re: Main Stored procedure to execute multiple procedures [message #669360 is a reply to message #669355] Tue, 17 April 2018 06:26 Go to previous message
EdStevens
Messages: 922
Registered: September 2013
Senior Member
senmng wrote on Mon, 16 April 2018 16:01
Also we have some limitations in using oracle utilities and we are advised to use pretty straight forward oracle DBMS scheduler jobs not chain jobs.
That makes no sense at all. Why would you be restricted from using any feature of the database and instead required to write your own version of the same feature?

As for "capture and store the exception handling in an error table", each individual procedure can easily do that as part of it's own error handling before raising the error to the caller.

What is really going on here? What you've posted so far has all the hallmarks of a pre-conceived (and ill-conceived) solution to an un-defined or ill-defined problem.

And as Cookiemonster said, your posted code isn't even close to being valid PL/SQL.
Previous Topic: SQL statement with averages
Next Topic: modification date for package SP
Goto Forum:
  


Current Time: Wed Apr 25 01:46:40 CDT 2018

Total time taken to generate the page: 0.12140 seconds