What is the point of dbms_lob.createtemporary()?

oracle plsql lob
eaolson picture eaolson · Jan 19, 2014 · Viewed 17.7k times · Source

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.

Answer

Wernfried Domscheit picture Wernfried Domscheit · Jan 19, 2014

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.

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More