During my earlier work as a consultant and in my todays job as a technical supporter to the development team I encountered various SQL & PL/SQL antipatterns. I would like to share some of them with you.
One of the worst things that may be seen often in PL/SQL code is using an empty string as a substitution of a NULL value. There are places where it technically works and therefore is simply ugly and others where it is just wrong.
Ugly NULL substitutions
The ugly usages are those where an empty string is used as an assignment to a variable or a parameter. Even if this is not technically wrong it may be a reason why inexperienced colleagues may end substituting NULL by empty string not only in assignements but also when it comes to conditions.
So ugly NULL substitutions look like this:
... L_Variable := ''; F_GetTableValues('P1' ,'P2' ,'' ,P3 ,'P4'); ... select decode(a.Column,'',0,a.OtherColumn) from table a; ... procedure OpenForm (P_UserId in varchar2 ,P_StartUpArgs in varchar2 default '' ,P_Commit in number default 0); ...
Wrong NULL substitutions
Substituting NULL by an empty string within a condition is not only ugly, it is just wrong and it will not lead to the expected results. The reason therefore is, that this kind of conditions will not work with the IS / IS NOT keyword (as needed for NULL comparison) but with an equal/unequal operator.
Obviously is is not really unknown, that comparing with an empty is string is kind of problematic, this might be the reason why we can often find things like:
if L_Variable = '' or L_Variable is null then ... end if; if L_Variable <> '' or L_Variable is not null then ... end if;
An example using the HR sample locations table
select city, country_id, state_province from locations where state_province = ''; no rows selected select city, country_id, state_province from locations where state_province is null; CITY COUNTRY_ID STATE_PROVINCE --------- ---------- -------------- Roma IT Venice IT Hiroshima JP Beijing CN Singapore SG London UK select city, country_id, state_province from locations where state_province <> ''; no rows selected
Maybe two single quotes are shorter than the keyword NULL (2 vs 4 letters) but as soon as we think about adding an additional or clause we should really get rid of the NULL substitutions.
And this please not only at the places where the substitutions is wrong but also where it is only ugly…
Knowing bad habits is the best way to avoid them.
Most of the examples I found are based on:
- copy/paste coding
- sticking to things that worked in the past (without questioning them)
- ignoring new features
This (and the other posts on the anti-patterns) should be a help to get over them.