How to handle Oracle CLOB variables efficiently

       

PROCEDURE Dummy (
   attr_   IN VARCHAR2 )
IS   
   xml_message_ CLOB;
   ...
BEGIN
   -- Initialize CLOB variable in the temp memory space
   IF ( NVL(DBMS_LOB.IsTemporary(xml_message_), 0) = 0 ) THEN                     
      DBMS_LOB.CreateTemporary(xml_message_, FALSE, DBMS_LOB.CALL);
   END IF;

   -- Business Logic

   -- Free the resource
   IF ( NVL(DBMS_LOB.IsTemporary(xml_message_), 0) = 1 ) THEN
      DBMS_LOB.FreeTemporary(xml_message_);
   END IF;

EXCEPTION
   WHEN OTHERS THEN
      
   -- Free the resource
   IF ( NVL(DBMS_LOB.IsTemporary(xml_message_), 0) = 1 ) THEN
      DBMS_LOB.FreeTemporary(xml_message_);
   END IF;

   RAISE;
END Dummy;

       
 

Important things to consider when using CLOBS.

* Once the CLOB variables are allocated in the memory, oracle will cleanup the resource once code block is completed. Therefore its not necessary to write the cleanup codes explicitly. But due to the bugs in the oracle database itself, if you haven't applied the latest patches, you might ended-up in memory leaks. So its always safe to write clenup codes but make sure to test the CLOB variable is not empty before calling the "DBMS_LOB.FreeTemporary(xml_message_);". Otherwise this will result in an exception.