CODEWITHSUNDEEP
oracle-databaseclob
Hank Gay
Hank Gay

Reputation: 71939

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

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.

Upvotes: 22

Views: 89024

Answers (5)

Iliachh Shaikh
Iliachh Shaikh

Reputation: 11

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;

Upvotes: 1

user6735282
user6735282

Reputation: 9

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

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

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

Upvotes: 18

Steve K
Steve K

Reputation: 19586

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

Upvotes: 22

Igor Zelaya
Igor Zelaya

Reputation: 4277

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;

Upvotes: 3

Related Questions