How to write block to skip failure procedure

Question

I having three procedure as below

CREATE OR REPLACE PROCEDURE p1(p_a IN NUMBER)
AS
BEGIN
 dbms_output.put_line(p_a || 'is a number');
END;
/

CREATE OR REPLACE PROCEDURE p2(p_b IN VARCHAR2)
AS
BEGIN
 dbms_output.put_line(p_b || 'is a word');
END;
/

CREATE OR REPLACE PROCEDURE p3(p_c IN DATE)
AS
BEGIN
 dbms_output.put_line(p_c || 'is a date');
END;
/

The above procedure are not logically related, but i want to write one anonymous block in which procedure p1 runs successfully run and procedure p2 having some error means procedure p3 will not run normally. But i want to write one anonymous block which will make procedure p3 to run even procedure p2 fails.

How to write anonymous block for that?

can we go for DBMS_SCHEDULER?

anonymous block could also have EXECPTION block. That's the point — Lukasz Szozda, Dec 21 '19 at 18:02
i know the method of procedure to have an exception block will handle error. But i want a anonymous block like if ```p1 fails p2 and p3 have to run``` or if ```p2 fails p1 and p3 should run``` or wise versa — Nvr, Dec 22 '19 at 03:40
It won't be an issue? Just wrap every call of SP with anonymous block with exception? If it fails it won't fail the main anononymous block and continue to run — Lukasz Szozda, Dec 22 '19 at 09:01
*I want to write one anonymous block in which procedure p1 runs successfully and procedure p2 having some error means procedure p3 will not run normally* - this is the default behaviour. *I want to write one anonymous block which will make procedure p3 to run even procedure p2 fails* - you can achieve this with exception handlers as in Belayer's answer. — William Robertson, Dec 22 '19 at 15:46

score 1 · Answer 1 · answered Dec 22 '19 at 08:25

That block posted by @dmak2709 produces your desired result. An exception within p1, p2, or p3 does not effect execution of the others. But you can also handle those exceptions within an overall anonymous block. Not only can the anonymous block have an exception section, any block within it can also. So you can include the call to each procedure within it's own nested block.

    begin              -- overall anonymous block
        begin          -- inner block for p1 
          p1(1) 
        exception 
          when others 
          then dbms_out.put_line('Error within p1');
        end ;          -- inner block for p1

        begin          -- inner block for p2 
          p2('Hello World');
        exception 
          when others 
          then dbms_out.put_line('Error within p2');
        end ;          -- inner block for p2

        begin          -- inner block for p3 
          p3(sysdate) 
        exception 
          when others 
          then dbms_out.put_line('Error within p3');
        end ;          -- inner block for p3

   exception           -- exception for  overall anonymous block
   when others 
   then dbms_out.put_line('Error within overall anonymous block');

    end ;              -- overall anonymous block

With that if any of the procedures encounters an exception the other procedures still execute. However any exception between those blocks will bypass any of the following procedure call blocks and any other code between the the procedure blocks. The is no 'between the blocks' code in this overall anonymous block.

Please look at the answer that i got from your solution. I still not getting expected answer. — Nvr, Dec 24 '19 at 18:00

How to write block to skip failure procedure

1 Answers1