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