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: 19
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: 26057
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: 19
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: 7443
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: 19
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: 19
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: 7443
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: 13165
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 messageGo to next message
EdStevens
Messages: 947
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.
Re: Main Stored procedure to execute multiple procedures [message #669604 is a reply to message #669360] Wed, 02 May 2018 11:07 Go to previous messageGo to next message
senmng
Messages: 19
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 #669605 is a reply to message #669604] Wed, 02 May 2018 11:56 Go to previous messageGo to next message
JPBoileau
Messages: 26
Registered: September 2017
Junior Member
Good start, but in your model, you will not know which Proc failed.

CREATE OR REPLACE PROCEDURE RUN_JOBS AS
BEGIN
   BEGIN
      Procedure1;
   EXCEPTIONS
   WHEN OTHERS THEN
      rollback;
      record_error('Proc1 failed');
      raise;
   END;

   BEGIN
      Procedure2;
   EXCEPTIONS
   WHEN OTHERS THEN
      rollback;
      record_error('Proc2 failed');
      raise;
   END;

   ... 
END;
Re: Main Stored procedure to execute multiple procedures [message #669606 is a reply to message #669604] Wed, 02 May 2018 12:59 Go to previous messageGo to next message
Michel Cadot
Messages: 65561
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your "error_log" procedure must be executed in an autonomous transaction otherwise you will get nothing in the log table as this will be rolled back:
SQL> create table error_log (message varchar2(4000));

Table created.

SQL> CREATE OR REPLACE PROCEDURE record_error
  2  IS
  3  begin
  4    insert into error_log values (
  5      sys.DBMS_UTILITY.format_error_backtrace || '
  6  ' || sys.DBMS_UTILITY.format_call_stack);
  7  end;
  8  /

Procedure created.

SQL> sho err
No errors.
SQL> CREATE OR REPLACE PROCEDURE RUN_JOBS AS
  2    v pls_integer := 0;
  3  begin
  4    v := 1 / v;
  5  EXCEPTION
  6  WHEN OTHERS THEN
  7  rollback;
  8  record_error();
  9  raise;
 10  END;
 11  /

Procedure created.

SQL> sho err
No errors.
SQL> exec run_jobs
BEGIN run_jobs; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "MICHEL.RUN_JOBS", line 9
ORA-06512: at line 1


SQL> select * from error_log;

no rows selected

Correction:
SQL> CREATE OR REPLACE PROCEDURE record_error
  2  IS
  3    pragma autonomous_transaction;   --<----
  4  begin
  5    insert into error_log values (
  6      sys.DBMS_UTILITY.format_error_backtrace || sys.DBMS_UTILITY.format_call_stack);
  7    commit;                          --<----
  8  end;
  9  /

Procedure created.

SQL> exec run_jobs
BEGIN run_jobs; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "MICHEL.RUN_JOBS", line 9
ORA-06512: at line 1


SQL> select * from error_log;
MESSAGE
--------------------------------------------------------
ORA-06512: at "MICHEL.RUN_JOBS", line 4
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
2B3BE460         1  anonymous block
28E25D2C         5  procedure MICHEL.RECORD_ERROR
2E10A6B8         8  procedure MICHEL.RUN_JOBS
28E7DC94         1  anonymous block

1 row selected.
Re: Main Stored procedure to execute multiple procedures [message #669608 is a reply to message #669606] Wed, 02 May 2018 13:51 Go to previous messageGo to next message
senmng
Messages: 19
Registered: April 2018
Junior Member
Yes Michel, i added pragma autonomous in my script

CREATE OR REPLACE PROCEDURE record_error

IS

PRAGMA AUTONOMOUS_TRANSACTION;

l_code PLS_INTEGER := SQLCODE;

l_mesg VARCHAR2(32767) := SQLERRM;

BEGIN

INSERT INTO error_table (error_code

, error_message

, created_on

, created_by)

VALUES (l_code

, l_mesg

, SYSDATE

, USER);

COMMIT;

END;

Re: Main Stored procedure to execute multiple procedures [message #669610 is a reply to message #669605] Wed, 02 May 2018 13:54 Go to previous messageGo to next message
senmng
Messages: 19
Registered: April 2018
Junior Member
Hi JPBoileau - Thanks for your suggestion. If we declare begin block for each and every procedure like you mentioned,will the proc name will get captured in the error table.If yes,in which column it will get captured?

If not,do we have the option to capture the proc name in the error table which throws the error.

record_error('Proc1 failed');
Re: Main Stored procedure to execute multiple procedures [message #669632 is a reply to message #669610] Fri, 04 May 2018 04:24 Go to previous messageGo to next message
cookiemonster
Messages: 13165
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 #669654 is a reply to message #669632] Sat, 05 May 2018 20:56 Go to previous messageGo to next message
senmng
Messages: 19
Registered: April 2018
Junior Member
Thank you Cookie Master. i have used error stack to capture the detailed error messages in my log table.

Also, how can we capture the proc name which can be passed as a parameter in our error table?

Re: Main Stored procedure to execute multiple procedures [message #669673 is a reply to message #669654] Mon, 07 May 2018 05:52 Go to previous message
cookiemonster
Messages: 13165
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.
Previous Topic: Sending table data in HTML format through PL/SQL
Next Topic: FORALL Bulk insert using variable
Goto Forum:
  


Current Time: Tue Jul 17 00:56:01 CDT 2018