Re: Null Clob or Isn't it?

Charlotte,

I'm fairly new to using CLOBs/BLOBs, but I know there is a difference between a NULL CLOB column and an empty CLOB column. Depending on the size of the data you wish to insert into your CLOB column, you may need to first create a locator that points to an empty CLOB value. This is done using the EMPTY_CLOB() function and at this point the CLOB column is not NULL, but has a length of 0. Here's an example that may help explain this a bit better.

mdt_at_testdb> Create table clob_test

   2 (col_a NUMBER,
   3 clob_col CLOB);

Table created.

mdt_at_testdb> insert into clob_test values(1, NULL);

1 row created.

mdt_at_testdb> insert into clob_test values(2, EMPTY_CLOB());

1 row created.

mdt_at_testdb> insert into clob_test values(3, 'Test CLOB text');

1 row created.

mdt_at_testdb> commit;

Commit complete.

mdt_at_testdb> select * from clob_test where clob_col is null;

      COL_A CLOB_COL

---------- ----------------------------------------------------
          1

mdt_at_testdb> select * from clob_test where length(clob_col) = 0;

      COL_A CLOB_COL

---------- ----------------------------------------------------
          2

mdt_at_testdb> select * from clob_test where clob_col is not null and length(clob_col) = 0;

      COL_A CLOB_COL

---------- ----------------------------------------------------
          2

mdt_at_testdb> select * from clob_test where length(clob_col) > 0;

      COL_A CLOB_COL

---------- ----------------------------------------------------
          3 Test CLOB text

So in the above example, the COL_A=2 row contains a CLOB that is both NOT NULL and has a length of 0.

Michael T.

Charlotte Hammond wrote:
> Hi All,
>
> I'm new to working with CLOBs. Any ideas on what I'm
> missing here?
>
> My query below returns a non-zero result:
>
> SELECT count(*)
> FROM my_table
> WHERE clob_column IS NOT NULL
> AND LENGTH(clob_column) = 0;
>
> How can I have a CLOB column of length zero which is
> not null?
>
> This is on 9.2.0.6
>
> Thanks
> Charlotte
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l