In a platform based on Oracle, I’m currently developing at work, there is a massive use of clobs so I must make sure to free all the resources allocated by clobs. To do this I created two procedures : one created a temporary clob, the other one frees the clob.
PROCEDURE pr_Open_CLOB(pc_Clob IN OUT NOCOPY CLOB) AS
BEGIN
pc_Clob:=EMPTY_CLOB;
DBMS_LOB.CREATETEMPORARY(pc_Clob, TRUE);
DBMS_LOB.OPEN(pc_Clob, DBMS_LOB.LOB_READWRITE);
END pr_Open_CLOB;
PROCEDURE pr_Close_CLOB(pc_Clob IN OUT NOCOPY CLOB) AS
BEGIN
IF DBMS_LOB.ISOPEN(pc_Clob)=1 THEN
DBMS_LOB.CLOSE(pc_Clob);
END IF;
IF DBMS_LOB.ISTEMPORARY(pc_Clob)=1 THEN
DBMS_LOB.FREETEMPORARY(pc_Clob);
END IF;
END pr_Close_CLOB;
This is how the 2 procedures should be used :
PROCEDURE pr_Test AS
c_Temp CLOB;
BEGIN
pr_Open_Clob(c_Temp);
--use clob
pr_Close_Clob(c_Temp);
EXCEPTION
WHEN OTHERS THEN
pr_Close_Clob(c_Temp);
END pr_Test;
In this way I make sure resources allocated by clobs are always released, even in case of exceptions.
