Temporary LOBs are used just like any other local variable and are used when you need to perform some operations on LOB data without having to store it in the database.

To create a temporary LOB, use the DBMS_LOB.CREATETEMPORARY() procedure:

DBMS_LOB.CREATETEMPORARY(
      lob_loc   IN OUT NOCOPY {BLOB | CLOB CHARACTER SET ANY_CS}
    , cache     IN BOOLEAN
    , duration  IN PLS_INTEGER := DBMS_LOB.SESSION
)

DBMS_LOB.FREETEMPORARY(
    lob_loc IN OUT NOCOPY {BLOB | CLOB CHARACTER SET ANY_CS}
)

NOTE: If you were to subsequently assign this lob locator to another lob locator, the latter one is also freed and marked invalid.

DBMS_LOB.ISTEMPORARY(
    lob_loc IN {BLOB | CLOB CHARACTER SET ANY_CS}
)

CREATE OR REPLACE PROCEDURE testTemporaryLob
AS
    temporaryLob CLOB;
    amount       NUMBER;
    offset       NUMBER := 1;
    buffer       VARCHAR2(100) := 'Testing a temporary LOB. ';
    buffer2      VARCHAR2(100) := 'Appended data.';
BEGIN

    DBMS_OUTPUT.ENABLE(1000000);

    -- --------------------
    -- CREATE TEMPORARY LOB
    -- --------------------
    DBMS_LOB.CREATETEMPORARY(
          lob_loc => temporaryLob
        , cache   => true
        , dur     => dbms_lob.session
    );

    -- -------------------------------
    -- TEST TO SEE IF LOB IS TEMPORARY
    -- -------------------------------
    IF (DBMS_LOB.ISTEMPORARY(temporaryLob) = 1) THEN
        DBMS_OUTPUT.PUT_LINE('This is a temporary LOB.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('This is a persistent LOB.');
    END IF;

    -- -----------------------------------
    -- OPEN TEMPORARY LOB FOR READ / WRITE
    -- -----------------------------------
    DBMS_LOB.OPEN(
          lob_loc    => temporaryLob
        , open_mode  => DBMS_LOB.LOB_READWRITE
    );

    -- -----------------------------------
    -- WRITE SMALL BUFFER TO TEMPORARY LOB
    -- -----------------------------------
    DBMS_LOB.WRITE(
          lob_loc => temporaryLob
        , amount  => LENGTH(buffer)
        , offset  => offset
        , buffer  => buffer
    );

    -- --------------------------------------
    -- APPEND A SMALL BUFFER TO TEMPORARY LOB
    -- --------------------------------------
    DBMS_LOB.WRITEAPPEND(
          lob_loc => temporaryLob
        , amount  => LENGTH(buffer2)
        , buffer  => buffer2
    );

    -- -----------------------------------
    -- GET CURRENT LENGTH OF TEMPORARY LOB
    -- -----------------------------------
    DBMS_OUTPUT.PUT_LINE(
        'Current length of temporary LOB: ' || DBMS_LOB.GETLENGTH(temporaryLob)
    );

    -- -----------------------------------
    -- PRINT CONTENTS OF THE TEMPORARY LOB
    -- -----------------------------------
    DBMS_OUTPUT.PUT_LINE('Current content of temporary LOB:');
    DBMS_OUTPUT.PUT_LINE(DBMS_LOB.SUBSTR(temporaryLob, DBMS_LOB.GETLENGTH(temporaryLob), 1));

    -- --------------------------
    -- CLOSE / FREE TEMPORARY LOB
    -- --------------------------
    DBMS_LOB.CLOSE(lob_loc => temporaryLob);
    DBMS_LOB.FREETEMPORARY(lob_loc => temporaryLob);

END;
/

Procedure created.



SQL> exec testTemporaryLob
This is a temporary LOB.
Current length of temporary LOB: 39
Current content of temporary LOB:
Testing a temporary LOB. Appended data.

PL/SQL procedure successfully completed.