Tags

, ,

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

Why substitute?

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…

Conclusion

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.