The length of a CLOB is 0?

Question

I have one LONG variable in the front end and In back end column data type is CLOB, When commit occurs then the blank data (NULL) goes to the CLOB variable in back end. When we check the data in the back end the length is '0'; that means it contains some value but there is no value in CLOB variable that is NULL

Why does it have a length of 0?

Answer 1

This is what's documented:

The techniques you use when accessing a cell in a LOB column differ depending on the state of the given cell. A cell in a LOB Column can be in one of the following states:

• NULL

  The table cell is created, but the cell holds no locator or value.

• Empty

  A LOB instance with a locator exists in the cell, but it has no value. The length of the LOB is zero.

• Populated

  A LOB instance with a locator and a value exists in the cell.

Note that if the LOB (in this case a CLOB) is NULL then the length is NULL, but if a locator has been initialised then the length is 0.

This would indicate that you are not inserting NULL into your CLOB column, but instead the results of the EMPTY_CLOB() function. It's possible to demonstrate:

SQL> create table tmp_clob_test (
  2      a clob
  3    , b clob
  4    , c clob
  5      );

Table created.

SQL>
SQL> insert into tmp_clob_test values(empty_clob(), null, 'x');

1 row created.

SQL>
SQL>
SQL> select length(a) a
  2       , length(b) b
  3       , length(c) c
  4    from tmp_clob_test
  5         ;

         A          B          C
---------- ---------- ----------
         0                     1

It's worth noting that the LONG data type is deprecated; it's worth using CLOB on the front-end as well.

If you want to "fix" this then you can use the NULLIF() function:

SQL> select nullif(length(a), 0) as a, length(a) as a
  2    from tmp_clob_test;

         A          A
---------- ----------
                    0