PL/SQL exception handling: do nothing (ignore exception)

Question

This is a question I am asked very frequently. Since I couldn't find any exact duplicate on stackoverflow, I thought I'd post it as a reference.

Question: In PL/SQL, I know how to catch exceptions and execute code when they are caught, and how to propagate them to the calling block. For example, in the following procedure, the NO_DATA_FOUND exception is handled directly, while all other exceptions are raised to the calling block:

CREATE OR REPLACE PROCEDURE MY_PROCEDURE() 
IS
BEGIN
    do_stuff();

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Do something
        handle_exception();

    WHEN OTHERS THEN
        -- Propagate exception
        RAISE;
END;

But what command should I use to ignore one or all raised exceptions and return execution control back to the calling block?

Solution 1

While I agree that 99% of the time it is bad practice to silently ignore exceptions without at least logging them somewhere, there are specific situations where this is perfectly acceptable.

In these situations, NULL is your friend:

[...]
EXCEPTION

    WHEN OTHERS THEN
        NULL;
END;

Two typical situations where ignoring exceptions might be desirable are:

1) Your code contains a statement which you know will fail occasionally and you don't want this fact to interrupt your program flow. In this case, you should enclose you statement in a nested block, as the following example shows:

CREATE OR REPLACE PROCEDURE MY_PROCEDURE() 
IS
    l_empoyee_name  EMPLOYEES.EMPLOYEE_NAME%TYPE;
BEGIN
    -- Catch potential NO_DATA_FOUND exception and continue
    BEGIN 
        SELECT EMPLOYEE_NAME
        INTO l_empoyee_name
        FROM EMPLOYEES
        WHERE EMPLOYEE_ID = 12345;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            NULL;
        WHEN OTHERS THEN
            RAISE;
    END;

    do_stuff();

EXCEPTION

    WHEN OTHERS THEN
        -- Propagate exception
        RAISE;
END;

Note that PL/SQL generally does not allow for the On Error Resume Next type of exception handling known from Visual Basic, where all exceptions are ignored and the program continues to run as if nothing happened (see On error resume next type of error handling in PL/SQL oracle). You need to explicitly enclose potentially failing statements in a nested block.

2) Your procedure is so unimportant that ignoring all exceptions it throws will not affect your main program logic. (However, this is very rarely the case and can often result in a debugging nightmare in the long run)

BEGIN

    do_stuff();

EXCEPTION

    WHEN OTHERS THEN
        -- Ignore all exceptions and return control to calling block
        NULL;
END;

Solution 2

Another scenario when it does make sense to silently ignore exception: When you call a script that is expected to create an object if it does not exist, and you do not have a create-or-replace syntax for that object. PLSQL objects have a create-or-replace syntax, but tables and indexes do not. Then we can put such scripts in a block and ignore the raised exception.

Comments

[William Robertson]

Perhaps worth adding that when others then null is considered poor practice except in very specific blocks where you really don't care whether a step worked or not, and when others then raise just throws away information about the error.