ORA-21780 Maximum Number Of Object Durations Exceeded: Using A Loop With EXECUTE IMMEDIATE And XMLTYPE
Last updated on AUGUST 30, 2017
Applies to:
PL/SQL - Version 10.2.0.5 and laterInformation 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;
/
(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