Archie poked around in his company’s billing system. This was a mistake, because that system is implemented largely in PL/SQL, and only a developer made from the sternest stuff can deal with PL/SQL.

Like most PL/SQL applications, the Oracle database is the closest thing they have to version control. Each developer makes changes in the live dev environment and then hopes for the best. They don’t use TOAD or SQLPlus or any “normal” tool for making these changes- they have to use an in-house developed GUI, because that GUI tracks their changes and writes rows into a database called “VersionDB”. When they finish a patch and want to release the changes to their customer sites, they send a copy of the VersionDB and let a simple script apply all of those changes.

It works about as well as you’d expect.

Now, it’s important to note that Oracle defaults to using ANSI NULLs, which means 

NULL != NULL
. I note this, because it means if you have an old version of a row, and a new version of a row, you can’t simply determine if they’re different by using code in the form: 

IF oldValue != newValue THEN
  /* do some stuff */
END IF;

If both oldValue and newValue are NULL, this code will mistakenly think that they’ve been changed. The obvious solution then, is to throw some NULL guards: 

IF NOT oldValue IS NULL AND oldValue != newValue THEN
  /* do some stuff */
END IF;

The problem with this is that the intent of the code is no longer clear. So our next instinct is to build some sort of function to handle this. That’s not a bad instinct, but it can lead to some very bad results… 

--**********************
-- Changed() - Public
--  Indicates if the value has been changed.
--
  procedure Changed (
    a_OldValue      varchar2,
    a_NewValue      varchar2,
    a_Changed     in out boolean
  ) is
  begin
    a_Changed := a_OldValue is null and a_NewValue is not null or
                 a_NewValue is null and a_OldValue is not null or
                 a_OldValue != a_NewValue;
  end Changed;
--
--**********************
-- Changed() - Public
--  Indicates if the value has been changed.
--
  procedure Changed (
    a_OldValue      number,
    a_NewValue      number,
    a_Changed     in out boolean
  ) is
  begin
    a_Changed := a_OldValue is null and a_NewValue is not null or
                 a_NewValue is null and a_OldValue is not null or
                 a_OldValue != a_NewValue;
  end Changed;
--
--**********************
-- Changed() - Public
--  Indicates if the value has been changed.
--
  procedure Changed (
    a_OldValue      date,
    a_NewValue      date,
    a_Changed     in out boolean
  ) is
  begin
    a_Changed := a_OldValue is null and a_NewValue is not null or
                 a_NewValue is null and a_OldValue is not null or
                 a_OldValue != a_NewValue;
  end Changed;
--
--**********************
-- Changed() - Public
--  Indicates if the value has been changed.
--
  procedure Changed (
    a_OldValue      timestamp with time zone,
    a_NewValue      timestamp with time zone,
    a_Changed     in out boolean
  ) is
  begin
    a_Changed := a_OldValue is null and a_NewValue is not null or
                 a_NewValue is null and a_OldValue is not null or
                 a_OldValue != a_NewValue;
  end Changed;
--
--**********************
-- Changed() - Public
--  Indicates if the value has been changed.
--
  function Changed (
    a_OldValue      varchar2,
    a_NewValue      varchar2
  ) return boolean is
    t_Changed boolean;
  begin
    Changed(a_OldValue, a_NewValue, t_Changed);
    --
    return t_Changed;
  end Changed;
--
--**********************
-- Changed() - Public
--  Indicates if the value has been changed.
--
  function Changed (
    a_OldValue      number,
    a_NewValue      number
  ) return boolean is
    t_Changed boolean;
  begin
    Changed(a_OldValue, a_NewValue, t_Changed);
    --
    return t_Changed;
  end Changed;
--
--**********************
-- Changed() - Public
--  Indicates if the value has been changed.
--
  function Changed (
    a_OldValue      date,
    a_NewValue      date
  ) return boolean is
    t_Changed boolean;
  begin
    Changed(a_OldValue, a_NewValue, t_Changed);
    --
    return t_Changed;
  end Changed;
--
--**********************
-- Changed() - Public
--  Indicates if the value has been changed.
--
  function Changed (
    a_OldValue                            timestamp with time zone,
    a_NewValue                            timestamp with time zone
  ) return boolean is
    t_Changed boolean;
  begin
    Changed(a_OldValue, a_NewValue, t_Changed);
    --
    return t_Changed;
  end Changed;

I particularly like the utility of having both PROCEDURE versions with output parameters and FUNCTION versions that wrap them. The real kicker is that, with all of that code, the Changed functions are only called about a dozen times in the program.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!