DBMS_XMLSTORE.insertxml on a Table with a Blob Column Leaks Memory (Doc ID 1609943.1)

Last updated on JULY 20, 2021

Applies to:

Symptoms

 Using DBMS_XMLSTORE.insertxml to insert records in a table that contain a BLOB.
This causes temp tablespace to grow until it is full.

The problem occurs only when the table in which the row is added contains a blob.
The temporary tablespace memory used is not released even after a commit. It
is only released after the oracle session is closed.

Problem can be easily reproduced using the below testcase -

CREATE TABLE "TEST_XML_BLOB" ("MYBLOB" BLOB );

declare
p_clob_val CLOB;
Num_byte_xml_data integer;
vl_ctx DBMS_XMLSTORE.ctxtype;
vl_rows pls_integer;
tablename varchar(30):='TEST_XML_BLOB';
Num_of_iter number;
xmlbuf varchar(4096) :='
                      <ROWSET>
                             <ROW>
<MYBLOB>0D0A3A32303A30303030353736300D0A</MYBLOB>
                             </ROW>
                      </ROWSET>';
begin

   EXECUTE IMMEDIATE 'TRUNCATE TABLE TEST_XML_BLOB';

   DBMS_LOB.CREATETEMPORARY(p_clob_val, TRUE, 1);
   DBMS_LOB.OPEN (p_clob_val, DBMS_LOB.LOB_READWRITE);

   Num_byte_xml_data :=lengthb(xmlbuf);

   DBMS_LOB.WRITE (p_clob_val, Num_byte_xml_data, 1, xmlbuf);
   --Num_of_iter  := 1;

   for i in 1..10000loop
         vl_ctx := DBMS_XMLSTORE.newcontext(tablename);
         vl_rows := DBMS_XMLSTORE.insertxml(vl_ctx, p_clob_val);
         DBMS_XMLSTORE.closecontext(vl_ctx);
   end loop;
   DBMS_LOB.CLOSE (p_clob_val);

   DBMS_LOB.FREETEMPORARY(p_clob_val);
   dbms_output.put_line(xmlbuf);
   dbms_output.put_line(Num_byte_xml_data);
commit;
   EXCEPTION
       when others then
            begin
                 dbms_output.put_line('Error - '||SQLCODE||' Message -
'||SQLERRM);
            end;
end;
/

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.