How to Handle Exceptions And Still Continue to Process a PL/SQL Procedure (Doc ID 1297175.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version: 10.1.0.3 and later   [Release: 10.1 and later ]
Information in this document applies to any platform.

Goal

When using nested Loops, how to process the exception of the inner loop so that it continues processing instead of stopping the entire process?

Here is an example of code that has an outer loops that iterates 20 times. During the iteration if an Exception is encountered at any time the entire process stops.  This example, encounters an Exception, the Exception is handled but the procedure exits. The goal is to allow the outer loop to complete all 20 iterations even if an Exception occurs.

The following sample is designed to LOOP 20 times.
Within the LOOP there is a MOD function so that every 5 iterations a NO_DATA_FOUND exception is raised.
In this sample, only 5 iterations are completed then the exception handler is encountered and the entire procedure is exited.
The DBMS_OUTPUT outside the loop is not encountered due to the exception.



CREATE OR REPLACE procedure Main_Run  IS
   V_MOD NUMBER;
   V_COUNTER NUMBER;
BEGIN
   FOR V_Loop IN 1..20 LOOP
         V_COUNTER := V_LOOP;
         DBMS_OUTPUT.PUT_LINE ('Loop number ' || v_loop);
         V_MOD := MOD(V_LOOP, 5);
         IF (V_MOD = 0) THEN
             RAISE NO_DATA_FOUND;
         END IF;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE ('Back in the outer section after the exception.');
EXCEPTION
   WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No Data Found Exception was raised');
   WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Failure');
END;
/


The following shows the output. Notice that once the Exception is encountered the procedure exits.

SQL> execute main_run
Loop number 1
Loop number 2
Loop number 3
Loop number 4
Loop number 5
No Data Found Exception was raised

PL/SQL procedure successfully completed.


The question is how to enable the procedure to finish all 20 iterations even if an Exception is encountered?

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms