oracle-databaseplsqllob

What is the point of dbms_lob.createtemporary()?

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.

Solution

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