Re: Null Clob or Isn't it?
- From: Michael Twaddell <twaddell@xxxxxxxxxxxx>
- To: charlottejanehammond@xxxxxxxxx
- Date: Thu, 07 Sep 2006 06:28:16 -0500
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@testdb> Create table clob_test
2 (col_a NUMBER,
3 clob_col CLOB);
Table created.
mdt@testdb> insert into clob_test values(1, NULL);
1 row created.
mdt@testdb> insert into clob_test values(2, EMPTY_CLOB());
1 row created.
mdt@testdb> insert into clob_test values(3, 'Test CLOB text');
1 row created.
mdt@testdb> commit;
Commit complete.
mdt@testdb> select * from clob_test where clob_col is null;
COL_A CLOB_COL
---------- ----------------------------------------------------
1
mdt@testdb> select * from clob_test where length(clob_col) = 0;
COL_A CLOB_COL
---------- ----------------------------------------------------
2
mdt@testdb> select * from clob_test where clob_col is not null and
length(clob_col) = 0;
COL_A CLOB_COL
---------- ----------------------------------------------------
2
mdt@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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
