ORA-22337 Thrown By Xmltype_var.toobject(), If Xmltype_var Contains Altered Subtype As Attribute (Doc ID 1640389.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Symptoms

Toobject() does not work, when applied to an xmltype which contains an altered subtype as an attribute.

1) create two types tmp_top, tmp_sub, where tmp_sub is an attribute of tmp_top.

2) store an instance of tmp_top as xmltype in a table

3) alter tmp_sub

4) try to call toobject() on the stored xmltype and get Exception ORA-22337

Expected result would be an instance of tmp_top

 

SQL> CREATE OR REPLACE TYPE "TMP_SUB" AUTHID CURRENT_USER AS OBJECT ( MYVALUE VARCHAR2(9) ) NOT FINAL;
 
Type created.


SQL> CREATE OR REPLACE TYPE "TMP_TOP" AUTHID CURRENT_USER AS OBJECT ( MYID NUMBER, MYSUB TMP_SUB ); 

Type created.

SQL> create table tmp_22337 (my_xml XMLTYPE);

Table created.

SQL> INSERT INTO  tmp_22337 (my_xml) VALUES(XMLTYPE(TMP_TOP(1,TMP_SUB('abcvalue'))));

1 row created.

SQL> commit;

Commit complete.



SQL> DECLARE
        l_xml XMLTYPE;
        l_check tmp_top;
     BEGIN

         SELECT my_xml INTO l_xml FROM tmp_22337;

         l_xml.toobject(l_check );

         dbms_output.put_line('toobject() yields: ' || l_check.mysub.myvalue);

           END; 

  PL/SQL procedure successfully completed.

SQL> ALTER TYPE TMP_SUB MODIFY ATTRIBUTE MYVALUE VARCHAR2(25) CASCADE INCLUDING TABLE DATA;

Type altered.

SQL> DECLARE
        l_xml XMLTYPE;
        l_check tmp_top;
     BEGIN
        SELECT my_xml INTO l_xml FROM tmp_22337;
        l_xml.toobject(l_check );
        dbms_output.put_line('toobject() yields: ' || l_check.mysub.myvalue);

         END; 

DECLARE

*

ERROR at line 1:

ORA-22337: the type of accessed object has been evolved

ORA-22337: the type of accessed object has been evolved

ORA-06512: at "SYS.XMLTYPE", line 196

ORA-06512: at line 6

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