Today I noticed that an empty clob string, i.e. a string with length 0, isn't always null.
According to the documentation it should be:
"Note: 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."
You can start checking your legacy code, it took me 1 hour for I found out why my code was failing :)
SQL>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL>declare
2 x clob;
3 begin
4 x := 'x';
5 x := substr( x, 3 );
6 if x is null
7 then
8 dbms_output.put_line( 'null' );
9 else
10 dbms_output.put_line( 'not null, lenght = ' || length( x ) );
11 end if;
12 end;
13 /
not null, lenght = 0
PL/SQL procedure successfully completed.
SQL>
SQL>declare
2 x varchar2(10);
3 begin
4 x := 'x';
5 x := substr( x, 3 );
6 if x is null
7 then
8 dbms_output.put_line( 'null' );
9 else
10 dbms_output.put_line( 'not null, lenght = ' || length( x ) );
11 end if;
12 end;
13 /
null
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>select nvl2( tst, 'not null: length = ' || length( tst ), 'null' )
2 from ( select substr( to_clob( 'x' ), 2 ) tst from dual );
NVL2(TST,'NOTNULL:LENGTH='||LENGTH(TST),'NULL')
--------------------------------------------------------------------------------
not null: length = 0
SQL>