What is the point of dbms_lob.createtemporary()?

Question

I don't quite get the point of the dbms_lob.createtemporary() function. How is:

DECLARE
    myclob CLOB;
BEGIN
    myclob := 'foo';
END;

any different from:

DECLARE
    myclob CLOB;
BEGIN
    dbms_lob.createtemporary( myclob, TRUE );
    myclob := 'foo';
    dbms_lob.freetemporary( myclob );
END;

I'm assuming the actions in between the create and free calls make it relevant, but I'm just not clear on how.

Probably, `dbms_lob.freetemporary` releases memory block (which may be huge enough) without waiting for variable goes out of scope, so you can control memory usage in a bit more flexible way. — Egor Skriptunoff, Jan 19 '14 at 17:57

Wernfried Domscheit · Accepted Answer · 2016-11-25T13:24:20.980

In earlier times you were always forced to use DBMS_LOB package. Then in Oracle 10g a feature called "SQL semantics for LOB" was introduced and now you can use the simplified syntax.

In CREATETEMPORARY you can specify the duration of the LOB in SQL semantic you cannot.

In your case the LOB is freed in the same way, i.e. when you leave the scope (i.e. the PL/SQL block) where the LOB is declared, then it is freed.

When you use FREETEMPORARY then the temporary segment in tablespace is released, when you call myclob := NULL; it will be kept.

What is the point of dbms_lob.createtemporary()?

1 Answers1