13 November 2009

An Empty Clob is not NULL, it's NOT NULL

Oracle recommends using CLOB instead of LONG for columns in the database. We all know this, right? Using CLOB is a lot easier than trying to manipulate LONG. Makes our life a lot easier.
But there is something about using CLOBs that I didn't know. As you might have guessed from the title it has to do with NULL...

First let's set up a table for testing:
SQL> create table test
  2  (id   number
  3  ,text clob
  4  );

Table created.


Along with some sample data:

SQL> insert into test values (1, null);

1 row created.

SQL> 
SQL> insert into test values (2, 'some data is here');

1 row created.

SQL> 
SQL> insert into test values (3, empty_clob());

1 row created.

SQL> 
SQL> commit;

Commit complete.


At first I expected that this query would show the records with the ID 1 and 3, but this is not the case.
SQL> select *
  2    from test
  3   where text is null
  4  /

        ID TEXT
---------- -------------
         1

An Empty_Clob is not NULL. According to the documentation:

EMPTY means that the LOB is initialized, but not populated with data.

When you run the following query, you can see this

SQL> select *
  2    from test
  3   where text is not null
  4  /

        ID TEXT
---------- -----------------
         2 some data is here
         3


Unfortunately we can't use the DUMP function to see what the Empty_Clob is made up of.
SQL> select dump (text)
  2    from test
  3  /
select dump (text)
             *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB


So how can you execute a SQL statement to find the NULL and the Empty_Clob?

One way would be to look at the length of the CLOB column, just like
SQL> select id
  2       , text
  3    from test
  4   where length (text) = 0
  5  /

        ID TEXT
---------- ------------------
         3

But then you would only select the Empty_Clob, not the NULL. Adding an extra predicate could solve this.
SQL> select id
  2       , text
  3    from test
  4   where length (text) = 0
  5      or text is null
  6  /

        ID TEXT
---------- ------------------
         1
         3


Another way to do this, is using NULLIF. NULLIF is one of those "obscure" functions that are rarely used, at least I hardly ever see this function used.
SQL> select id
  2       , text
  3    from test
  4   where nullif (length (text), 0) is null
  5  /

        ID TEXT
---------- ----------------------------------
         1
         3


Personally I like this use of NULLIF. What do you think?

Cleanup:
SQL> drop table test
  2  /

Table dropped.


Links to the Oracle Documentation:
DUMP
EMPTY_CLOB
NULLIF

4 comments:

  1. very nice solution Alex... I like the NULLIF and I must admit I hardly ever use that function, but now I've a nice use case ;)

    Thanks,
    Dimitri

    ReplyDelete
  2. You can kinda see why there has been a little resistance to the take-up of LOBs. There are a few little gotchas like this that jump out and surprise you when you least expect it. :)

    Cheers

    Tim...

    ReplyDelete
  3. Hi Alex,

    Maybe not the quickest solution, but this works too:

    select *
    from test
    where dbms_lob.compare(nvl(text, empty_clob()),empty_clob())= 0

    see ya
    Peter

    ReplyDelete
  4. Just thought I'd let you know that you can use "dump" on clobs e.g.

    dump(anydata.convertclob(text))

    Cheers
    Matt

    ReplyDelete