What is difference between empty_clob and createtemporary in Oracle?

Question

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.

Do you have an example error message of a function not excepting an `empty_clob`? This works for me: `SELECT dbms_lob.getlength(empty_clob()) FROM dual;` — wolφi, May 31 '20 at 15:55

wolφi · Accepted Answer · 2020-05-31T16:22:19.710

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.

What is difference between empty_clob and createtemporary in Oracle?

1 Answers1