” = empty string and/or NULL…

How many times have you seen '' used for a NULL string?

Oracle 9i SQL Reference Release 2 (9.2) – “Basic Elements of Oracle SQL, 5 of 10: Nulls”

“…Oracle currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.”

Simplify your SQL – Avoid Unnecessary Joins
Did you know that NULL is a “Boolean value”? Hmmm…

Comments

  1. Going further back.

    “Oracle7 currently treats a character value with a length of zero as null. However, this may not continue to be true in future versions of Oracle7.”

  2. Jeffrey Kemp
    4 May 2010 - 9:24 am

    It’s hard to see how Oracle would go about making this change to Oracle now. Perhaps it’d be a matter of adding a new attribute for character columns that causes empty strings to be stored differently to NULLs. In addition, they’d have to make changes to the way that SQL and PL/SQL treat character strings. For convenience they might add a new system parameter that controls the default behaviour for newly-created tables.

    Of course, anyone wishing to take advantage of this change would have to carefully look at any existing code. If they’ve been setting strings to NULL instead of ” they’ll be fine. The most pernicious code will be dynamic SQL – how often have we seen this sort of code:

    EXECUTE IMMEDIATE ‘insert into mytable values (”’ || nullable_variable || ”’);’;

Leave a Reply

Your email address will not be published / Required fields are marked *