The PL/SQL Compiler warning “PLW-06009: procedure “string” OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR” was introduced with 11g release 1.
It’s purpose is to alert you to the fact that you have a WHEN OTHERS exception handler and the last statement within it doesn’t contain a RAISE or a RAISE_APPLICATION_ERROR statement.
The first example shows what you will see if you turned the compiler warnings off.
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL' /
and compile the following procedure:
CREATE OR REPLACE PROCEDURE p IS BEGIN RAISE no_data_found; EXCEPTION WHEN OTHERS THEN NULL; END p;
The important thing to note is that on line 7 the WHEN OTHERS statement does nothing with the exception, effectively suppressing it.
Creating the procedure in SQLPlus I see a “clean” compile:
Now I will enable PL/SQL Compiler warnings:
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL' /
and recompile the procedure
ALTER PROCEDURE p COMPILE /
This time I can see I haven’t had such a “clean” compile:
The show errors command displays the following:
As you can see the PLW-06009 warning is shown. I can then choose ignore the warning or amend the procedure to fix the problem.
If you agree with Tom Kyte and believe that WHEN OTHERS without any kind of RAISE statement is a bug then as with all compiler warnings you can turn the warning into an error and stop the program unit from compiling.
I change the warning to an error: (In this example I am changing the parameter at session level but it can be changed at the instance level)
ALTER SESSION SET PLSQL_warnings = 'ERROR:06009' /
Now when I try to compile my procedure I see that it is invalid and will not compile until I fix the WHEN OTHERS issue.
Here is the revised version of the procedure that fixes the PLW-06009 warning. The only change required was to replace NULL with RAISE on line 9.
CREATE OR REPLACE PROCEDURE p IS BEGIN RAISE no_data_found; EXCEPTION WHEN OTHERS THEN RAISE; END p;
Running this version of the procedure and I see that the PLW-06009 warning no longer appears.
More information about PL/SQL compile time warnings can be found in the documentation.
In this article I have explained what the PLW-06009 compiler warning is and how it can be enabled both as a warning and an error.