Mark Old PL/SQL Code as "Not For Use":12 Things Developers Will Love About Oracle Database 12c Release 2 Part 11

Times change. New code quickly becomes legacy code. And legacy code is often superseded by better, faster code. So you deprecate the old code.

But this brings a problem:

How do you stop people using the legacy modules?

People tend to stick with what they know. Even after you’ve repeatedly told everyone to move to the new module there’s always (at least) one developer who insists on using the deprecated procedure instead of the newer, shinier option.

And in complex applications it’s tough to keep track of what’s obsolete.

This is tough to solve.

So to help you with the deprecation process, we’ve introduced a new pragma for this.

To use it, place

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

below the retired section.

Great. But how does it help?

We’ve added a bunch of new PL/SQL warnings: PLW-6019 to PLW-6022. Enable these and Oracle will tell you if you’re using deprecated code:

alter session set plsql_warnings = 'enable:(6019,6020,6021,6022)';
create or replace procedure your_old_code is 
  pragma deprecate ( 
    your_old_code, 
    'This is deprecated. Use new_code instead!' 
  ); 
  begin 
    null; 
  end your_old_code; 
/ 
show err 
Warning(2,3): PLW-06019: entity YOUR_OLD_CODE is deprecated

This is great. But we’ve all been ignoring the “AUTHID DEFINER” warning forever! If code is truly obsolete, it would be good if you could stop people using it all together.

Fortunately you can!

Here’s the great thing about warnings. You can upgrade them to be errors! PLW-6020 is thrown when you write code calling a deprecated item. Set this to error and the offending code won’t compile:

alter session set plsql_warnings = 'error:6020'; 
create or replace procedure calling_old_code is 
begin 
  your_old_code(); 
end calling_old_code; 
/ 
show err 
3/3 PLS-06020: reference to a deprecated entity: 
  This is deprecated. Use new_code instead!

Of course, if you turn PLW-6020 into an error system wide, a lot of stuff might break! So you can selectively upgrade it on given objects:

alter procedure calling_old_code compile 
  plsql_warnings = 'error:6020' reuse settings;

So now you have the power to force others to stop using pre-historic code.

Full article originally published at blogs.oracle.com on November 10, 2016.