Main Stored procedure to execute multiple procedures [message #669343] |
Mon, 16 April 2018 11:44 |
|
senmng
Messages: 22 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 #669604 is a reply to message #669360] |
Wed, 02 May 2018 11:07 |
|
senmng
Messages: 22 Registered: April 2018
|
Junior Member |
|
|
Hi - I have created a main proc which can call all the sub procs to execute it in sequentially and capturing the exception by means of separate procedure record error which again inserts the error message in an error table.
CREATE OR REPLACE PROCEDURE RUN_JOBS AS
BEGIN
Procedure1;
Procedure2;
Procedure3;
Procedure4;
Procedure5;
EXCEPTIONS
WHEN OTHERS THEN
rollback;
record_error();
raise;
END;
/
---------------- The proc to capture and store the error message values in the error_log table______________________
CREATE OR REPLACE PROCEDURE record_error
IS
l_code PLS_INTEGER := SQLCODE;
l_mesg VARCHAR2(32767) := SQLERRM;
BEGIN
INSERT INTO error_log (error_code
, error_message
, backtrace
, callstack
, created_on
, created_by)
VALUES (l_code
, l_mesg
, sys.DBMS_UTILITY.format_error_backtrace
, sys.DBMS_UTILITY.format_call_stack
, SYSDATE
, USER);
|
|
|
|
|
|
|
Re: Main Stored procedure to execute multiple procedures [message #669632 is a reply to message #669610] |
Fri, 04 May 2018 04:24 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
JPBoileau is suggesting you pass the procedure name as a parameter, add a column to the log table to hold it and modify the insert appropriately.
However, what I would is store the output of DBMS_UTILITY.FORMAT_ERROR_STACK and DBMS_UTILITY.format_error_backtrace in the log table.
Those two will give you the full error stack and backtrace so you can see exactly which line in which procedure/function/package threw the error.
|
|
|
|
Re: Main Stored procedure to execute multiple procedures [message #669673 is a reply to message #669654] |
Mon, 07 May 2018 05:52 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
By writing code that passes it as a parameter then uses that parameter in the insert.
There's no magic trick here you just treat it as any other bit of data.
But you don't need it. The call stack will show the object and line number where the error happened, you can see from that which procedure it was.
|
|
|