As an application evolves the code gets refactored and some of the code may no longer be needed. Removing old code can be a problem as far as backwards compatibility is concerned, but you want to make sure any new development avoids referencing old APIs. Oracle database 12.2 introduced the
DEPRECATE
pragma to mark code as deprecated and display compiler warnings if deprecated code is referenced by any newly compiled code.
Basics
Compiler warnings must be enabled for us to see the results of the
DEPRECATE
pragma.
-- Enable just deprecated code warnings.
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:(6019,6020,6021,6022)';
-- Enable all warnings.
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
The
DEPRECATE
pragma has two variations. Without an additional literal the standard messages are displayed. Not surprisingly, when we compile the deprecated code we see a message.
CREATE OR REPLACE PROCEDURE my_proc AUTHID DEFINER AS
PRAGMA DEPRECATE(my_proc);
BEGIN
NULL;
SP2-0804: Procedure created with compilation warnings
SQL> SHOW ERRORS
Errors for PROCEDURE MY_PROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3 PLW-06019: entity MY_PROC is deprecated
More importantly, if we write some code that references the deprecated procedure we will see a warning.
CREATE OR REPLACE PROCEDURE my_calling_proc AUTHID DEFINER AS
BEGIN
my_proc;
SP2-0804: Procedure created with compilation warnings
SQL> SHOW ERRORS
Errors for PROCEDURE MY_CALLING_PROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3 PLW-06020: reference to a deprecated entity: MY_PROC declared in
unit MY_PROC[1,11]
We can use a literal string to alter the compiler warning message, which can be used to indicate the API we should have called rather than the deprecated API.
CREATE OR REPLACE PROCEDURE my_proc AUTHID DEFINER AS
PRAGMA DEPRECATE(my_proc, 'Use MY_PROC2 instead of MY_PROC.');
BEGIN
NULL;
SP2-0804: Procedure created with compilation warnings
SQL> SHOW ERRORS
Errors for PROCEDURE MY_PROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3 PLW-06019: entity MY_PROC is deprecated
CREATE OR REPLACE PROCEDURE my_calling_proc AUTHID DEFINER AS
BEGIN
my_proc;
Errors for PROCEDURE MY_CALLING_PROC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3 PLW-06020: reference to a deprecated entity: MY_PROC declared in
unit MY_PROC[1,11].
Use MY_PROC2 instead of MY_PROC.
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/3 PLW-06019: entity MY_PACKAGE is deprecated
We can mark individual items in the package as deprecated, rather than the whole package.
CREATE OR REPLACE PACKAGE my_package AUTHID DEFINER AS
PROCEDURE my_procedure;
PRAGMA DEPRECATE(my_procedure);
PROCEDURE my_procedure(p_in IN NUMBER);
FUNCTION my_function RETURN NUMBER;
END my_package;
SP2-0808: Package created with compilation warnings
SQL> SHOW ERRORS
Errors for PACKAGE MY_PACKAGE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3 PLW-06019: entity MY_PROCEDURE is deprecated
We can also mark package cursors, types, variables and exceptions as deprecated.
CREATE OR REPLACE PACKAGE my_package AUTHID DEFINER AS
CURSOR c_cursor IS
SELECT dummy FROM dual;
PRAGMA DEPRECATE(c_cursor);
TYPE t_rec IS RECORD (
id NUMBER,
description VARCHAR2(50)
PRAGMA DEPRECATE(t_rec);
g_var NUMBER := 1;
PRAGMA DEPRECATE(g_var);
ex_my_exception EXCEPTION;
PRAGMA DEPRECATE (ex_my_exception);
END my_package;
SP2-0808: Package created with compilation warnings
SQL> SHOW ERRORS
Errors for PACKAGE MY_PACKAGE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLW-05021: exception EX_MY_EXCEPTION does not have a pragma
EXCEPTION_INIT
4/3 PLW-06019: entity C_CURSOR is deprecated
10/3 PLW-06019: entity T_REC is deprecated
12/9 PLW-06026: package specification exposes global variable
13/3 PLW-06019: entity G_VAR is deprecated
16/3 PLW-06019: entity EX_MY_EXCEPTION is deprecated
Hope this helps. Regards Tim...
