We help IT Professionals succeed at work.

Execute Multiple Procedures From a wrapper Procedure.

Kamal Agnihotri
on
I have 5 prcocedures. Each of which are compiled and execute and produce the desired result when run individually.

I created a "wrapper" procedure which I would like to call the procedures individually. This procedure works erratically. Soem times Only once procedure will work, some times two will work.

The procedure looks like this:

Create Or Replace Procedure Main_Proc_01 As
   Begin
             Attach_Procedure_A;
             Attach_Procedure_B;
             Attach_Procedure_C;
             Attach_Procedure_D;
             Attach_Procedure_E;
 End;
/

--##################

Execute Main_Proc_01;

Is not running the 5 procedures one by one. Sometimes, only one would run, sometimes 3 etc.

None of the procedures require any parameters. All of them run fine individually and retun the expected result.

Please advise.

Thanks a lot.
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
My guess is that one of them generates an error and that will kill the calling procedure.

What you need to do is properly handle each exception in its own pl/sql block.

The example below ignores ALL errors and moves to the next.  This may not be what you want.

Create Or Replace Procedure Main_Proc_01 As
    Begin
	Begin
              Attach_Procedure_A;
		Exception when others then null;
	End;
	Begin
              Attach_Procedure_B;
		Exception when others then null;
	End;
	Begin
              Attach_Procedure_C;
		Exception when others then null;
	End;
	Begin
              Attach_Procedure_D;
		Exception when others then null;
	End;
	Begin
              Attach_Procedure_E;
		Exception when others then null;
	End;
  End;
 /

Open in new window

Author

Commented:
Slightwv,

Thanks a lot. Your suggestion fixed the issue. I will close the question.

If you have a suggestion on a PL/SQL programming Book, which also elaborates on the latest techniques in creation of Cursors, Triggers, Packages, Handeling Exeptions, dynamic SQL, that is welome.

Once again, thanks a lot.
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
The online documentation is probably the best place to start.

I've not touched a technical book for many years so cannot recommend any.

Look for anything by Steven Feuerstein.

He has a LOT of PL/SQL stuff on his site:
http://www.stevenfeuerstein.com/



A quick check also has a book of his by my preferred publisher.

I like O'Reilly Media books.

The bad news is, this book is pretty old but then again, PL/SQL hasn't changed much.
http://shop.oreilly.com/product/9780596001216.do

There is this one as well:
http://www.amazon.com/Oracle-SQL-Programming-Steven-Feuerstein/dp/1449324452