Oracle SQL functions that are related to LOBs

Some Oracle SQL functions that are related to LOBs include

empty_blob() and empty_clob()
to_blob(), to_clob(), to_nclob() and to_lob

EMPTY_BLOB and EMPTY_CLOB

empty_blob() and empty_clob() return an empty LOB locator.

The LOB locators that are returned by these two functions can only(?) be used in insert and update statements, they cannot be passed to the functions or procedures of dbms_lob.

Compare with dbms_lob.createTemporary().

Example

A zero sized varchar2 is not distinguishable from null in Oracle, a clob with an empty string is different from null:

create table tq84_c (id integer, c clob);

begin
   insert into tq84_c values (1, null        );
   insert into tq84_c values (2, ''          );
   insert into tq84_c values (3, empty_clob());
   commit;
end;
/

select
   id,
   length(c) len
from
   tq84_c;
-- 
--   ID   LEN
-- ----  ----
--    1      
--    2      
--    3     0

drop table tq84_c;