Why does SQLPLUS show empty and empty CLOB as null?

Question

SQLPLUS seems to show zeros and empty lines for the CLOB in a way that I did not expect.

Try the following in SQLPLUS (I am using Oracle 10g server). Create a table with CLOB and insert zero, 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, let's make some queries, but first we need to say SQLPLUS to show us the unique values:

 set null {NULL} 

In the following query, I would expect only row 1 to return, but it returns 2:

 select * from tableA where field2 is null; field1 field 2 ----------------------- 1 {NULL} 3 {NULL} 

Hmm, why is '' stored as null in the CLOB?

Ok, so based on this result, I expect the following query to return all 3 rows, but will only show {NULL} in lines 1 and 3. 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 zeros, although I expected 1. So, what is going on here? Does set null for the CLOB, and if so, what should I use instead?

I am actually trying to solve another problem with null CLOB values, but this confusing behavior made me run rings for a while, so I would like to understand this before proceeding.

Thank you 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 why the string type defaults to VARCHAR2 rather than VARCHAR .

They are the same in all currently released versions, but VARCHAR not recommended. This is due to the fact that VARCHAR must distinguish between NULL and an empty string, and VARCHAR2 , which is not described in the standards, is not.

Answer 2

I think this is due to the way SQL * Plus handles empty strings and null values.

empty_clob() does not create a NULL value, but a CLOB of length 0.

This is confirmed by the fact that row 2 is not returned when you request rows where field2 is null.

Using my JDBC tool, I can highlight columns with zero color with a different color, and there the row with empty_clob () is not highlighted, while the other two.

Therefore, I would say that this is an incorrect processing of the set null option in SQL * Plus. With the following statement, you will see the difference:

  select field1, 
        nvl (field2, 'THIS IS NULL')
 from tableA;

For me, this displays:

  field1 field 2
 -----------------------
 1 THIS IS NULL 
 2        
 3 THIS IS NULL

Answer 3

It would be interesting to see what really is on line 2. Perhaps this is not the case as per the documentation (see below) empty_clob () will return an initialized clob location that has no data (somewhat unclear in the null problem).

When executing SQL statements in SQL query tools, although Oracle tends to implicitly convert CLOBs to strings that are truncated at some arbitrary length.

goal

EMPTY_BLOB and EMPTY_CLOB return an empty LOB locator that can be used to initialize a LOB variable or in an INSERT or UPDATE statement to initialize a column or LOB attribute in EMPTY. EMPTY means that the LOB is initialized, but not populated with data.