Archie poked around in his companys 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 dont 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 youd expect.

Now, its 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 cant simply determine if theyre 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 theyve 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. Thats 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.

http://thedailywtf.com/articles/ch-ch-ch-changes