ORA-21780 Maximum Number Of Object Durations Exceeded: Using A Loop With EXECUTE IMMEDIATE And XMLTYPE

(Doc ID 1345804.1)

Last updated on AUGUST 30, 2017

Applies to:

PL/SQL - Version 10.2.0.5 and later
Information in this document applies to any platform.
***Checked for relevance on 24-Aug-2017***

Symptoms

Populating an XMLSEQUENCETYPE (SYS.XMLTYPE) via an EXECUTE IMMEDIATE in a loop fails on approx 65k iterations with

ORA-21780 "Maximum number of object durations exceeded."

Works when calling the procedure directly instead of using Execute Immediate.

Below is an example to demonstrate the error:

create or replace PROCEDURE load_offers
    (p_offers IN xmlsequencetype
    )
IS
begin
    return;
end load_offers;
/


declare

  xmlstring varchar2(2000);
  tn_messages     xmlsequencetype := xmlsequencetype();

  tn_xml_tab               xmlsequencetype;
  t_stmt                   varchar2(1000);

begin

   xmlstring :=  '<?xml version="1.0" encoding="UTF-8"?><SupplierOffer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://xxx.com/test.xsd"> </SupplierOffer>';

   tn_messages.extend( 1 );
   tn_messages(1) := xmltype(xmlstring);

  for i in 1 .. 65472  --this is beyond the object duration limit of 65741
  loop
    begin

      if i > 65400 then  --this shows what iteration causes the error
                         -- the number can vary but will be around this mark.
        dbms_output.put_line('###Start LOOP: '||i);
      end if;

      tn_xml_tab := tn_messages;
      t_stmt := 'begin load_offers(:xml_tab); end;';
      execute immediate t_stmt using tn_xml_tab;
    end;
  end loop;
end;
/

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms