PL/SQL 201: Ensuring backward compatibility in your PL/SQL code

Unlike other code in the stack, database-level programs such as those built in PL/SQL, tend to have a very long half-life. In part, that's because companies are far less likely to change their database technology than user interface language.

And that means that it is especially important for Oracle Database programmers to prioritizing writing maintainable, well-documented programs. It is also critical that as we make changes to our code base, we don't break programs that have been working for years.

In other words, whenever possible, enhanced code should be backward compatible with earlier versions of code. That's called backward compatibility. In this post, I will explore features of PL/SQL that make it easy to ensure your latest and greatest code has maximum compatibility with "the past."

If a tree falls in a forest and no one is around to hear it, does it make a sound?

You've heard that before, right? Well, how about this one:

If you write a program and no one ever uses it, did you really write that program?

I ask myself this a lot. I've written hundreds of "demo" programs over the years, for trainings, for play, for libraries. But, hey, maybe nobody ever used them or ran them. Did I ever really write it? Oh, sure I did! Did it really matter? Maybe just to me.

Anyway, chances are that you don't write a program unless it is going to be used. And as soon as "someone" uses your code (it could even by you, with a new packaged procedure calling an existing subprogram), you've implicitly signed a contract with that person (or other program unit), stating:

I will do everything in my power to ensure that as I make changes in the future, I will honor how the program works for you now.

So let's see how we can keep our promises, by taking a look at what I just had to do today to my code.

Most of the production PL/SQL code I've written since 2010 is for the PL/SQL Challenge, a website offering weekly quizzes on SQL, PL/SQL and more (try it out!). The backend code uses the "qdb" prefix for "Quiz DataBase". And I created a package to manage player information.

You can play weekly quizzes competitively or just for fun. So I created the set_competitive_status procedure to toggle between them. In other words, the first time it is called for my user ID, it sets up to compete (my answers are ranked). The next it is called, my status is set to "for fun only."

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
END;

And for six years it worked just fine. In other words, it is used, both in the Oracle Application Express "front-end" and in other packages. Here's an example from the backend:

PROCEDURE qdb_process_answers
   AUTHID DEFINER
IS
BEGIN
   qdb_player_mgr.set_competitive_status (
      user_id_in          => 1506,
      competition_id_in   => 15709);
END;

And from APEX:

We are now, though, working on a new "skin" for the PL/SQL Challenge: the Oracle Dev Gym. This site has a different flow when taking a quiz, and I needed to be able to force the competitive status to "for fun only" - even if it was the first time, even if it was already set that way. In other words, my "toggle" procedure should not toggle.

First, let's see what happens if I push ahead somewhat blindly, paying no attention to the past. "Hey, no problem, I will just add a parameter!"

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in           IN INTEGER,
      competition_id_in    IN INTEGER,
      force_non_compete_in IN BOOLEAN);
END;

I compile the package with the modified specification. No errors! Exciting! Even before I get to changing the implementation of this enhancement, though, I notice that the QDB_PROCESS_ANSWERS procedure has an INVALID status. That makes sense - I changed a program unit (QDB_PLAYER_MGR) on which the procedure relies.

No worries. I will just recompile the procedure. But when I do, I see this error:

PLS-00306: wrong number or types of arguments in call to 'SET_COMPETITIVE_STATUS' 

Um, still no worries? Well....I guess not. I will just change QDB_PROCESS_ANSWERS to pass a Boolean value for the third argument:

PROCEDURE qdb_process_answers AUTHID DEFINER
IS
BEGIN
   qdb_player_mgr.set_competitive_status (
      user_id_in           => 1506,
      competition_id_in    => 15709,
      force_non_compete_in => FALSE);
END;

Now it compiles without errors. Problem solved.

No! No! No! That is almost certainly the wrong thing to do. Why?

1. This procedure does not need the "force non compete" functionality. It's fine the way it is.

2. What if there are hundreds of invocations of this procedure in my code base? Am I going to search them all out (which I can do quite nicely with PL/Scope) and change each one, even though they do not need the new functionality?

3. Even worse, am I going to deal with the outraged howls of other developers on my team whose code now will not compile?

So what can I do instead?

1. Provide a default value for the new parameter: if it is a trailing IN parameter, then I can assign a default value, and all existing invocations of that subprogram will recompile successfully:

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in           IN INTEGER,
      competition_id_in    IN INTEGER,
      force_non_compete_in IN BOOLEAN DEFAULT FALSE);
END;

2. Create an overloading of that procedure (same name, different parameter list):

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
      
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER,
      force_non_compete_in IN BOOLEAN);
END;

Now, when QDB_PROCESS_ANSWERS is recompiled, the PL/SQL compiler will automatically determine which of the SET_COMPETITIVE_STATUS subprograms "match" the invocation in the procedure.

3. Create an entirely new procedure for this new requirement, as in:

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
      
   PROCEDURE turn_off_ranking (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
END;

So which should you do? It depends (when doesn't it "depend"? :-) ).

You will not be able to simply add a parameter with a default value if the new parameter is an OUT or IN OUT parameter.(only IN parameters can have default values). In this case, you will need an overloading. For example:

PACKAGE qdb_player_mgr AUTHID DEFINER
is
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER);
      
   PROCEDURE set_competitive_status (
      user_id_in          IN INTEGER,
      competition_id_in   IN INTEGER,
      new_status_out OUT VARCHAR2);
END;

If adding that single new IN parameter actually means making a whole lot of changes in the existing procedure, it will likely make the most sense to create a new subprogram dedicated to that strikingly different algorithm.

To Conclude (and offer some additional advice)

1. When changing the header of a subprogram already in use, honor the current signature, and do everything you can to avoid having to change existing invocations to match the new signature. Use default parameter values or overloading. Which, by the way, is another reminder of a baseline best practice for PL/SQL: put all your procedures and functions in packages. Do not create schema-level procedures and functions. You can't overload at the schema level.

2. Determine whether the changes made to the implementation of the subprogram (its executable section) may affect the way the subprogram is currently used. For example, if it is a function that is executed from within a SQL statement, then you could cause all sorts of problems by inserting a SQL statement into the function, especially non-query DML (inserts, updates, deletes).

3. If a subprogram that is currently in use is being replaced entirely by a new, shinier, better version, mark the original subprogram as deprecated with the DEPRECATE pragma (new to 12.2).