Tag Archives: Compiler Error

ORACLE 12.2 – deprecated pragma for PL/SQL units

02 Sunday Oct 2016

Posted by in ORACLE 12c Release 2, PL/SQL

≈ Leave a comment

Tags

, , , ,

With ORACLE 12.2 it is possible to mark program units as deprecated along with a message using a new pragma. The message will show up when the deprecated program unit is referenced and the referencing program unit is compiled (and compiler warnings are enabled).

This enables us to gently replace code by advising the user to move to an alternative program unit.

 

EXAMPLE: Standalond procedure

ALTER SESSION SET PLSQL_WARNINGS = 'Enable:All'
/

CREATE OR REPLACE PROCEDURE p IS
   PRAGMA deprecate(p,'p is deprecated please use p2 instead.');
BEGIN
   sys.dbms_output.put_line('p');
END p;
/

Warning(2,4): PLW-06019: entity P is deprecated

When compiling a deprecated program unit we get an PLW-06019 warning.

EXAMPLE: Packaged procedure

CREATE OR REPLACE PACKAGE pkg IS
   PROCEDURE p;
     PRAGMA deprecate(p,'pkg.p is deprecated please use pkg.p2 instead.');
 
   PROCEDURE p2;
END;
/

Warning(2,4): PLW-06019: entity P is deprecated

CREATE OR REPLACE PACKAGE BODY pkg IS
   PROCEDURE p IS
   BEGIN
      sys.dbms_output.put_line('pkg.p');
   END p;

   PROCEDURE p2 IS
   BEGIN
      sys.dbms_output.put_line('pkg.p2');
   END p2;
END pkg;
/

The pragma specification for packaged program units has to be placed in the specification.

 

Referencing a deprecated program unit

So, what happens if you are using pkg.p or p?

CREATE OR REPLACE PROCEDURE q IS
BEGIN
   p();
   sys.dbms_output.put_line('q');
 
   pkg.p();
   sys.dbms_output.put_line('q'); 
END q;
/

Warning(3,4): PLW-06020: reference to a deprecated entity: p is deprecated please use p2 instead.
Warning(6,8): PLW-06020: reference to a deprecated entity: pkg.p is deprecated please use pkg.p2 instead.

A different warning (PLW-06020 instead of PLW-06019) shows up including the message we placed in the pragma.

Forcing PLW-06020 to be an error

If we would like to force the user of pkg.p/p to move to pkg.p2/p2 we could now easily defined PLW-06020 to be a compiler error.

ALTER SESSION SET PLSQL_WARNINGS = 'ERROR:6020'
/

CREATE OR REPLACE PROCEDURE q IS
BEGIN
   p();
   sys.dbms_output.put_line('q');
 
   pkg.p();
   sys.dbms_output.put_line('q'); 
END q;
/

Error(8,4): PLS-06020: reference to a deprecated entity: p is deprecated please use p2 instead.
Error(11,8): PLS-06020: reference to a deprecated entity: pkg.p is deprecated please use pkg.p2 instead.

So the q procedure does not compile anymore.

Conclusion

The deprecated pragma is a good way to let others know that a program unit should not be used anymore…if:

  • compiler warnings are enable
  • compiler warnings are looked at

The possibility to turn a warning into an error is also helpful to force the replacement of deprecated program units (in dev environment).

The problem I see is, that not all deprecated program units have the same expiry date and therefor setting a warning type to error for this case is somehow not sufficient. What I really would like to have is a third (optional) parameter in the pragma specification where I could specify by which date THIS deprecation turns into an error.