Oracle 11g: WHEN OTHERS THEN NULL
Posted by decipherinfosys on January 31, 2008
As most of you know, we can enable compile time warnings to make programs more robust and to avoid run time error(s) later on. These warnings do not prevent the routines from compiling but it gives compile time warnings. This is just to alert an end user that there could be a potential problem. We need to use ‘PLSQL_WARNINGS’ initialization parameter to enable the warnings. It can be altered at system level or session level.
In 11g, Oracle introduced a new warning message (PLW-06009) to warn against the most dangerous and the most ignored PL/SQL construct:
WHEN OTHERS THEN
This construct suppresses the most serious error, pretending like no error has occurred and the program finishes successfully. By enabling the compile time warning, all the sub-programs which use this construct get a compile time warning. As mentioned earlier, subprogram will be compiled successfully but it will display warning that “OTHERS” exception does not end in “RAISE” error. Let us first enable the compile time warning and then create a test procedure to demonstrate this.
SQL> ALTER SESSION SET PLSQL_WARNINGS=’ENABLE:ALL’;
Now let us create the following procedure.
CREATE OR REPLACE PROCEDURE TEST_PROC
WHERE col1 = ‘ORACLE’;
WHEN OTHERS THEN
SP2-0804: Procedure created with compilation warnings
As shown, it will be created with compilation warning. Let us see what warning it gives.
SQL> show errors
Errors for PROCEDURE TEST_PROC:
12/9 PLW-06009: procedure “TEST_PROC” OTHERS handler does not end in
RAISE or RAISE_APPLICATION_ERROR
This warning indicates that procedure has WHEN OTHERS exception block which does not use RAISE or RAISE_APPLICATION_ERROR clause and there could be potential problem at run time.
This warning is displayed only when PLSQL_WARNINGS is enabled. If it is disabled, then procedure will be compiled in normal way without giving any errors. Disable the warning by issuing following statement.
SQL> ALTER SESSION SET PLSQL_WARNINGS=’DISABLE:ALL’;
Re-create the stored procedure shown above and it will compile without any problem. Introducing warning for WHEN OTHERS is a good warning measure but it is ultimately the DBA/database developer who has to avoid such coding practices.
One Response to “Oracle 11g: WHEN OTHERS THEN NULL”
Sorry, the comment form is closed at this time.