It seems SQLPLUS shows nulls and empty strings for CLOBs in a way that I wasn't expecting.
Try the following in SQLPLUS (I'm using Oracle 10g Server). Create a table with a CLOB, and insert null, empty clob, and what I think of as an empty string:
create table tableA (field1 number, field2 clob);
insert into tableA values (1, null);
insert into tableA values (2, empty_clob());
insert into tableA values (3, '');
OK, lets do some queries, but first we need to tell SQLPLUS to show nulls clearly for us:
set null {NULL}
For the following query, I would have expected only row 1 returned, but it returns 2:
select * from tableA where field2 is null;
field1 field 2
-----------------------
1 {NULL}
3 {NULL}
Hmm, so ''
is stored as a null in a CLOB?
Ok, so based on that result, I would now expect the following query to return all 3 rows but show {NULL}
in rows 1 and 3 only. However I get this result:
select * from tableA;
field1 field 2
-----------------------
1 {NULL}
2 {NULL}
3 {NULL}
This is confusing. I thought there were only 2 nulls, even though I originally expected 1. So what's going on here? Does set null
not work for CLOBs, and if so what should I use instead?
I'm actually trying to solve a different problem with null CLOB values, but this confusing behaviour has had me running rings for a while, so I'd like to understand this before I continue.
Thanks in advance
Boz
Oracle
does not distinguish between NULL
and an empty string. This is a well-known violation or SQL
standards.
This is the reason of default string type being VARCHAR2
and not VARCHAR
.
In all currently released versions they are the same, but VARCHAR
is not recommended for use. This is because VARCHAR
is supposed to distinguish between NULL
and an empty string, while VARCHAR2
, not being described in the standards, is not.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With