Why does SQLPLUS show empty and null CLOBs as null?

Question

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

Answer 1

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.