oracle-databaseplsqllob

What is difference between empty_clob and createtemporary in Oracle?


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.


Solution

  • 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:

    enter image description here

    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.