12.2 Helps You Manage Persistent Code Base w/New Deprecate Pragma

"Persistent code base"? What's that?

Well, I suppose I could have said "aging" or "legacy" code base, but that doesn't capture my point, which starts with:

If I were a Javascript programmer, I would probably be excited if my code lasted more than a year.

As an Oracle Database developer, though, it is not unreasonable to expect that my code will be in production for years, perhaps decades.

Now, that's persistent code. And why is that?

Because, well, DATABASE.

The database is the repository for your enterprise. Certainly it contains the data, and if you fully leverage the database properly, it will also contain your business logic.

And while it is not all that painfully disruptive to rewrite your UI code, it can be business-threatening to do any of the following:
  • Switch your database technology
  • Upgrade your database software too quickly
  • Rewrite your business logic in a new scripting language 
So your database - data and associated code - tends to be the most stable layer in your stack, with upgrades applied with great care, and code maintained for many years.

And "maintained" could mean:
  • True legacy mode: no further enhancements, critical bug fixes only. This will always be true for at least a part of your code base, and I will not address it further in this post.
  • Actively used and enhanced: this is where the action is. Sure, lots of the code has been around for years, and will continue to be. Most of it works great and doesn't need to be touched. But then there are enhancements, bug fixes, new features.
Oh, and, inevitably, deprecation. Which is the topic of this blog post.

Many things happen to code that lasts a long, and one of them is that we come up with better ways to do things. The "better" part could be a better name for a subprogram, a seriously modified parameter list, or an entirely new implementation.

And, of course, what you'd really like to do is immediately get rid of the "old stuff" and make sure everyone's program units, across the entire application, use only the cool, new stuff.

Sadly, that's not always possible. Sure you can send out an email:
Hey, folks, listen up! Don't use pkg_a.proc_b anymore. We've got a much better version in pkg_c.proc_d.
But there's no guarantee that anyone (or, more to the point: everyone) will switch over to the new iteration. And since a published, in-use API is a sort of contract you signed with users, you can't just force them to change (usually).

So instead, the old stuff sticks around and might even get used again by a careless developer - even if you add a comment like:

   /* DON'T USE THIS! Use pkg_c.proc_d instead! */
   PROCEDURE proc_b;

Wouldn't it be nice if there was a way to document deprecated functionally, so that when your code is compiled, you would be notified (and could even have it flagged as a compile error) that you are using out-of-date stuff?

Yes, that sure would be lovely.

[Pretend you are now listing to a Drum Roll....]

Welcome to PL/SQL 12.2 and the DEPRECATED pragma.

You want to notify developers that proc_b is out of date and proc_d should be used instead?

This is now what you do:

   PROCEDURE proc_b;
      'pkg_a.proc_b deprecated. Use pkg_c.proc_d instead.');

Looks really similar to the comment version, doesn't it? So what, then, does this pragma do for you? Not much, if that's all you do.

But let's suppose that you've decided your team should "step it up" in terms of code quality and overall professionalism.

As a part of that initiative, you are going to take advantage of PL/SQL's compile-time warnings. You go into SQL Developer's preferences, type "compile" in the search field, and find this:

Yep, as suspected, you are not currently utilizing compile-time warnings. So let's start by enabling all of them:

Then when I compile pkg_a, I see this warning:

The compiler is simply notifying me that proc_b has been marked as deprecated. That's fine. But what happens when I try to use this deprecated subprogram?

I am now "on notice" for using a subprogram that should not be used.

Well, now are you thinking to yourself: "Big deal. That's easy to ignore."

True. But we can take things a step further: I can tell PL/SQL to treat that warning as an error:

ALTER SESSION SET plsql_warnings='ERROR:(6020)'

When I do this and compile my program unit, suddenly compilation fails. The warning (PLW-06020) has been transformed into an honest-to-goodness compile error (PLS-06020).

Of course when I do that, I have declared that all subprograms and program units (aka, packages) declared as deprecated via the pragma are no longer deprecated. They are "gone", off-limits, unusable.

In other words, you can easily and quickly (well, depending on how much code you've got) identify all program units still relying on deprecated functionality.

Just convert that warning to an error, then recompile your schema (or schemas) and see which ones end up invalid. As in:

ALTER SESSION SET plsql_warnings='ERROR:(6020)'

      schema          => USER /* Or another */,
      compile_all     => TRUE /* the default */,
      reuse_settings  => FALSE /* the default */);

As PL/SQL code bases grow and evolve over time - and they will, 'cause they are not "going anywhere" & play a mission critical role in your applications - structured, reportable deprecation will become more and more important....

So take advantage of this fine, new Oracle Database 12c Release 2 feature as soon as you can.

Which is today, if you sign up for the Oracle Database Exadata Express Cloud Service. Catchy name, right?

And no, you should resist the temptation to use an acronym. ADEECS just doesn't do it. :-)


  1. Hello Steven,

    Nice 12cR2 feature indeed :)

    However, if you already mentioned the "musical" name "ADEECS" ...
    From some web posts I understood that by now Oracle
    only offers version 12cR2 on the Cloud ...
    I think that this will simply cause a very high delay in adopting it ... especially considering the fact that big customers generally move very slowly, while the not so big ones probably haven't even invested in Exadata, which is not a cheap toy, as we know ...

    So, except for the customers who are always running fast
    and whose only concern is to be on the top of the technology,
    the others will wait patiently for 12cR2 to "descend from the Cloud back to the Earth" ... if this will even ever happen at all ...

    Thanks a lot & Best Regards,

  2. I suggest that you should look at this situation (first and currently only release of 12.2 on the cloud, later "on-premise") very differently: getting 12.2 out on Exadata Express is accelerating the process and time by which 12.2 will be generally available.

  3. Hello Steven,
    Maybe using the cloud might have a strong appeal on those who
    are considering their first adopting of an Oracle project,
    and maybe up to now they found administering a database by themselves as being too demanding ... anyway for a production database.
    But, at least from my own experience, very big applications usually also have a very complex infrastructure around them,
    considering all the elements and not just the database alone.
    So, migrating such an application to the Cloud is by itself
    not a small project at all.
    Life would be too easy if they could just "plug out" the database from its current place and plug it in into the Cloud,
    leaving everything else "as is" ...

    I am not a security specialist, but I guess than just dealing
    with the possible security issues in such a context is just another big project by itself ...
    and as far as I am aware, everybody today is "security minded"
    more than ever ...

    I personally would enjoy having a toy database of my own,
    managed completely for me by others, just readily put under my fingers ... of course, without the limitations of a LiveSQL-like toy ...

    But, maybe, the day will come ... one can never know ...

    And then ALL the professional (and expensive !) DBA-s will be considered useless, just like developers already are in so many places ...

    Thanks a lot & Best Regards,

    1. Hello Iudith,

      You certainly have a point and I share a lot of your concerns. But I think Oracle had very little choice. For too long, Oracle didn't really believe in the whole Cloud concept (they were making way too much money with their on-premise licenses). And to be honest, I'm still not convinced myself that it's such a good idea in the end (security, privacy, liability, provider dependency, internet dependency etc...). The future will show us. But the fact is that a large number of (potential) customers are making the move. I see it in my company as well. And Oracle is running behind, as competitors such as Amazon, Google and Microsoft started years ago and have a serious head start. There are several reasons why companies decide to move to the cloud. One of the reasons of course is less maintenance and administration. But there's more. One of the main reasons my company is making the move, is scalability. Analytics and big data (I hate that name as it means everything and nothing) have become increasingly important over the last couple of years. I also notice people are using it for goals it's actually not meant for. But the fact remains that data volumes and more importantly, processing needs can quickly grow, business cases and usage also change much more frequently. I also notice that we've become lazy in our designs. We no longer try to model our data warehouse specifically to fulfill users' demands as performantly as possible, as those demands can change from week to week and even the users of the data frequently change and they often have different needs. One day your 8 CPU's might be fine, but a month later you might need double that amount.

      Part 1/2

    2. And that brings me to another important concern with Oracle. Oracle simply is TOO expensive! You said it yourself, Exadata is wonderful, but it will cost you an arm and a leg. For years we've begged management for it, but it was deemed way too expensive. For years and years Oracle's pricing strategy worked. People complained and you spent tons on licensing, but in the end the database did its job, we were more or less happy and we paid. But times are changing, demands are changing. Could you imagine the licensing nightmare if you frequently had to double your CPU's? As a result, in a time were "data lake" is a new hype word, I see our managers and architects (it's not as if you've much say in the matter as a developer or even a DBA) moving to other solutions. Are those solutions really less expensive? I'm actually not that convinced, but it seems like it at first glance and the fact is it's often easier (read less expensive) to scale according to your needs. They now prefer pushing their data to Hadoop in Amazon AWS where queries are replaced by scala and spark functions, for example. Of course, you'll still need a relational database every now and then. But even there I see Oracle losing ground. After all, you can run Postgresql and other opensource or at least much cheaper alternatives in AWS as well. Let's be clear, these are not my decisions and as an Oracle professional with 15+ years experience I'm still convinced that it's a wonderful product that technically can answer most of our demands. But I can't argue with the pricing point. Oracle 12.1 brought us some wonderful new things such as In-Memory Column Store and the great pluggable database architecture. But both of these are extra licensing options, even for enterprise users, and as such of very little use to us. It's downright scandalous that you still have to pay for partitioning up till today. I'm convinced there will always be some business and security critical companies for which money is no issue that will have no problem spending all this money on the best Oracle has to offer. But those are a minority. And if Oracle wants to keep most of their other customers in the long term, they will have to drastically review their pricing and licensing strategies. And to come back to the reason for this reply, Exadata Express in the cloud seems to be a first step in that direction. But I'm afraid it's too little, too late. It will take more, much more to convince my managers and architects and make them turn back to Oracle.

      Kind regards,


      Part 2/2

    3. Erwin, I am optimistic that a number of the concerns you raised in your second post will be addressed with our cloud services. They do (Exadata Express) and will (future, fully matured DB services) offer lower price points and remove the need to pick and choose among options.


Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Get rid of mutating table trigger errors with the compound trigger

Quick Guide to User-Defined Types in Oracle PL/SQL