Mark PLSQL procedure/function as deprecated

Question

Some programming tools like C# or Java can mark procedures/functions as deprecated. But Oracle PLSQL is no built in functionality for this. I wonder there is another way to support this feature.

I want them to show like "The PL/SQL compiler warnings about any PL/SQL code being compiled.".

96/43    PLW-06005: inlining of call of procedure 'TEST' was done

Answer 1

In Oracle release 12.2, there is a new pragma for this:

pragma deprecate ( deprecated_thing, 'Message to other developers' );

... where deprecated_thing is any PL/SQL identifier. The following things can be deprecated:

• Subprograms
• Packages
• Variables
• Constants
• Types
• Subtypes
• Exceptions
• Cursors

For example:

CREATE OR REPLACE PACKAGE old_package AS
PRAGMA DEPRECATE(old_package , ’Package old_package has been deprecated in favor of new_package’);
    PROCEDURE p1;
    PROCEDURE p2;
END old_package;

Anyone trying to compile something that uses whatever you've deprecated will receive a PL/SQL warning in the range of PLW-6019 to PLW-6022.

You can enable these warnings as follows, so that you will see them when you try to compile code:

alter session set plsql_warnings = 'enable:(6019,6020,6021,6022)';

Alternatively, you can set your session to treat these warnings as errors:

alter session set plsql_warnings = 'error:6020';

Or, you can set the object itself to make anyone using it to get an error:

alter package <package with deprecated stuff> compile plsql_warnings = 'error:6020' reuse settings;

Answer 2

This is not a supported function of Oracle and the PL/SQL language. You would have to develop a sort of framework that would check variables at the beginning of execution to determine if the code was depreciated however that would all have to be manually handled by development team. This simply is not a feature yet included by Oracle.