Beware: an empty string in Oracle is NULL

Keywords:
empty string '' CLOB varchar varchar2 text null isnull nvl NullPointerException JDBC

Problem:
There's code that is (seemingly) working with writing strings to CLOB columns and with the code from a previous post (Convert Oracle CLOBs to String) the reading of strings from the CLOB columns is working ok too ... until we get to empty strings - could it be that something is converting '' to NULL?

Here's a test case:

create table test_clobtext(

      id   number

    , text clob

);

insert into test_clobtext values (1, 'some clob text');

insert into test_clobtext values (2, '');

select id, text from test_clobtext;

You get:

        ID TEXT

---------- ----------------

         1 some clob text

         2                  

What's the value in the 2nd row? You can use the NVL() (which is just like ISNULL()):

select id, NVL(text,'IT IS A NULL') as text from test_clobtext;

Shock, horror, this is the result:

        ID TEXT

---------- ----------------

         1 some clob text

         2 IT IS A NULL      

... and because I'm still in disbelief:

select id, NVL(text,'IT IS A NULL') as text from test_clobtext

where text IS NULL;

This is definitely the result:

        ID TEXT

---------- ----------------

         2 IT IS A NULL      

So it's something to do with CLOBs? No, changing the text column to a varchar or varchar2 and you will get the same result! Is this right?

Solution:
This is apparently a well known issue (that I've only just stumbled across). A discussion is on stackoverflow: Why does Oracle treat empty string as NULL which links to more details information on ask-tom: Strings of Zero Length Not Equivalent To NULL.

It would seem that there are some scenarios where it won't be NULL but I can't reproduce this - changing the test case to have text as a char(1) still gives me NULL for the column.

The bottom line is if you're working with strings/text in a Oracle database you must expect and handle NULL values coming back - there will be no way to distinguish between whether what was originally stored was actually a NULL or an empty string ('').

Notes:
If you're dealing with CLOB columns you do have the option of storing (vendor specific) empty_clob() where you do want to distinguish between the cell being set to empty from it not being set at all (ie NULL). This post "An Empty Clob is not NULL" is a good discussion.