Systems Engineering and RDBMS

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
NULL;

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’;

Session altered.

Now let us create the following procedure.

CREATE OR REPLACE PROCEDURE TEST_PROC
AS
v_col2 VARCHAR(10);
BEGIN

SELECT col2
INTO v_col2
FROM test_compute
WHERE col1 = ‘ORACLE’;

EXCEPTION
WHEN OTHERS THEN
NULL;

END;
/

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:

LINE/COL ERROR
——– ————————————————————
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”

  1. […] Oracle 11g: WHEN OTHERS THEN NULL « Systems Engineering … – Jan 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 …… […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: