Can anyone please tell what is the basic difference between empty_clob
and createtemporary
.
Here is my code.
/*empty_clob vs createtemporary()*/
DECLARE
elob CLOB;
tlob CLOB;
BEGIN
IF elob IS NULL THEN
dbms_output.put_line('elob is null');
ELSE
dbms_output.put_line('elob has a locator');
END IF;
IF tlob IS NULL THEN
dbms_output.put_line('tlob is null');
ELSE
dbms_output.put_line('tlob has a locator');
END IF;
elob := empty_clob;
dbms_lob.createtemporary(tlob, false);
IF elob IS NULL THEN
dbms_output.put_line('elob is null');
ELSE
dbms_output.put_line('elob has a locator');
END IF;
IF tlob IS NULL THEN
dbms_output.put_line('tlob is null');
ELSE
dbms_output.put_line('tlob has a locator');
END IF;
dbms_lob.freetemporary(elob); -- exception
dbms_lob.freetemporary(tlob);
END;
and why I can not pass an empty object locator which is returned by empty_clob
to any dbms_lob function/procedure.
A temporary lob is a full blown LOB, the only difference to a normal, filled LOB is that it is not associated with a table and that it lives in a temporary tablespace. See here for details. The purpose is to manipulate your LOB in the temp space until you are ready to store it in a normal table row.
However, empty_clob
initialized a LOB locator and creates a proper LOB of length 0. I copied Julian Dykes' example from his excellent presentation LOB Internals:
So you cannot call dbms_lob.freetemporary
on an empty_clob
, because it's not in the temp tablespace.
EDIT:
Once the empty_clob
is stored in a table, you can inspect it's LOBID:
CREATE TABLE t (c CLOB, e CLOB DEFAULT EMPTY_CLOB()) LOB (e) STORE AS SECUREFILE;
INSERT INTO t (c) VALUES (null);
SELECT DBMS_LOBUTIL.getinode(e).lobid AS lobid,
DBMS_LOBUTIL.getinode(e).length AS length,
DBMS_LOBUTIL.getinode(e).extents AS extents
FROM t;
LOBID LENGTH EXTENTS
0000000100000007E5E1 0 0
According to Julian, the LOBID is generated from a sequence, which means it is quite likely unique across the database.