How to Handle Exceptions And Still Continue to Process a PL/SQL Procedure
Last updated on JULY 05, 2017
Applies to:PL/SQL - Version: 10.1.0.3
Information in this document applies to any platform.
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
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
DBMS_OUTPUT.PUT_LINE ('Back in the outer section after the exception.');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No Data Found Exception was raised');
WHEN OTHERS THEN
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?
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