CODEWITHSUNDEEP
oracle-databaseplsqllob
Aman Singh Rajpoot
Aman Singh Rajpoot

Reputation: 1479

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.

Upvotes: 5

Views: 1850

Answers (1)

wolφi
wolφi

Reputation: 8361

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.

Upvotes: 2

Related Questions