CodeSOD: Ch-ch-ch-changes

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] Use NuGet or npm? Check out ProGet, the easy-to-use package repository that lets you host and manage your own personal or enterprise-wide NuGet feeds and npm repositories. It's got an impressively-featured free edition, too!