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
--------------------------------------------------------------------------------

Answer 2

If you are trying to do the comparison in PL/SQL, you can just test equality as Igor's solution does

SQL> ed
Wrote file afiedt.buf

  1  DECLARE
  2     dummy  clob;
  3  BEGIN
  4       dummy := empty_clob();
  5        IF dummy = empty_clob() THEN
  6           dbms_output.put_line( 'Dummy is empty' );
  7        ELSE
  8           dbms_output.put_line( 'Dummy is not empty' );
  9        END IF;
 10* END;
SQL> /
Dummy is empty

PL/SQL procedure successfully completed.

If you are trying to do this in SQL, thougyh, you need to use the DBMS_LOB.COMPARE function. A LOB column in a table is really a LOB locator (i.e. pointer), so what you really care about is that the value pointed to by the LOB is comparable to the value pointed to by the LOB locator returned by the EMPTY_CLOB() function.

SQL> desc bar
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------

 FOO                                                CLOB

SQL> insert into bar values ('123');

1 row created.

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

1 row created.

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

1 row created.

SQL> ed
Wrote file afiedt.buf

  1  select count(*)
  2    from bar
  3*  where dbms_lob.compare( foo, empty_clob() ) = 0
SQL> /

  COUNT(*)
----------
         2

SQL> ed
Wrote file afiedt.buf

  1  select count(*)
  2    from bar
  3*  where dbms_lob.compare( foo, empty_clob() ) != 0
SQL> /

  COUNT(*)
----------
         1

Answer 3

something like this should work for initialization:

DECLARE
   dummy  clob;
   dummy2 clob;
BEGIN
     dummy := empty_clob();
      IF dummy = empty_clob() THEN
         dummy2 := dummy;
      END IF;
END;

Answer 4

A simple way to test for empty clobs in SQLplus is to convert all the CLOBS to varchar2 (using the TO_CHAR function) before performing the test:

SELECT *
  FROM table1
  WHERE TO_CHAR(table1.column1) IS NULL

Answer 5

DECLARE
    dummy    CLOB := 'fxsgf';
    dummy1   CLOB;
BEGIN
    IF dummy1 = EMPTY_CLOB ()
    THEN
        DBMS_OUTPUT.put_line ('Dummy1 is empty');
    ELSE
        DBMS_OUTPUT.put_line ('Dummy1 is not empty');
    END IF;

    IF dummy = EMPTY_CLOB ()
    THEN
        DBMS_OUTPUT.put_line ('Dummy is empty');
    ELSE
        DBMS_OUTPUT.put_line ('Dummy is not empty');
    END IF;
END;