Author: John Simon

Oracle Compile Time Warnings:

Oracle Compile-time warnings are issued when the code is ambiguous or inefficient. Oracle Compile Time Warnings can help you make your programs robust by avoiding the run time errors. Oracle Compile Time Warnings does not stop your program from compiling rather only compile time warnings are given. Oracle Compile Time Warnings are meant to warn user about a potential problem. This is just to alert an end user that there could be a potential problem.

PLSQL_WARNINGS Parameter:

Oracle Compile-time warnings can be enabled by setting the PLSQL_WARNINGS parameter at either instance or session level.

Oracle 11g Compile-time Warning:

Oracle 11g has introduced a new compile-time warning PLW-06009. PLW-06009 warnings alerts the user against most dangerous PL/SQL construct “WHEN OTHERS THEN NONE”. The PL/SQL compiler in Oracle 11g gives warning if it encounters a when others exception without a raise or raise_application_error.

WHEN OTHERS THEN NONE Construct:

If you enable the compile time warning then all the sub-programs that are using this construct will get a compile time warning. The subprograms will be successfully compile but they will display a warning that “OTHERS” exception does not end in “RAISE” error.

Enable Oracle Compile Time Warning:

First we will enable the compile time warning

SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
Session altered.

Create Procedure:

Now we will create a procedure TEST_PROC.

CREATE OR REPLACE PROCEDURE MYPROCEDURE
AS
MYCOL VARCHAR(10);
BEGIN

SELECT col2
INTO MYCOL
FROM MYTABLE
WHERE col1 = ‘ORACLE';

EXCEPTION
WHEN OTHERS THEN
NULL;

END;
/

SP2-0804: Procedure created with compilation warnings

The procedure will be created but with a compilation warning. Now we will see the warning.

SQL> show errors
Errors for PROCEDURE MYPROCEDURE:

 LINE/COL                                                                          ERROR
————–   ————————————————————————————
     12/9        PLW-06009: procedure “MYPROCEDURE” OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

The PLW-06009 warning shows that our procedure has WHEN OTHERS exception block with no RAISE or RAISE_APPLICATION_ERROR clause. This could be a potential problem at run time.

PLW-06009 is a warning only and the compilation goes through fine. You can execute the procedure, but consider yourself warned. Remember we will see this warning only if we enable the PLSQL_WARNINGS parameter.

Disable Oracle Compile Time Warning:

If PLSQL_WARNINGS parameter is disabled then our procedure will be compiled in a normal way without giving any errors. You can disable the warning by running below command.

SQL> ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';

Now if we re-create the same stored procedure then it will compile without any problem but it can result in a potentially unstable code.

Conclusion:

To conclude I would recommend you to enable PLSQL_WARNINGS parameter. It is the responsibility of database developer/DBA that he avoids such practices that can results in havoc. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Recovery from an Unplanned Outage!!
   Efficient Tracking of Exceptions in Oracle!
   Great Tips to Resolve Tablespace Fragmentation Issues!!
   Inside Oracle Temporary Tablespace!!