What is the length of an empty string?
According to Oracle documentation,
Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls
And therefore the length of null is null, not 0 (there are no character value with a length of zero :oops:).
However, this is not true for clobs 😈
SQL> create table t(x clob); Table created. SQL> insert into t values (empty_clob()); 1 row created. SQL> select x,length(x) from t where x is not null; X LENGTH(X) - ---------- 0