How do I test if a column equals empty_clob() in Oracle?

Question

The naïve FOO = empty_clob() complains about incompatible types. I tried Googling, but (once again) had little success searching for help with Oracle. Thanks.

Answer 1

Are you just wanting to check for a CLOB that doesn't have any length? While not exactly what your asking, it's basically the same thing?

select *
  from bar
 where dbms_lob.getlength(foo) = 0;

Here is the complete test:

SQL> create table bar (foo clob);

Table created.

SQL> insert into bar values (empty_clob());

1 row created.

SQL> select *
  2    from bar
  3  where dbms_lob.getlength(foo) = 0;

FOO
--------------------------------------------------------------------------------