Insertion Of A New Record In A Schema-Based XML Table Fails With ORA-31000 For Non-Schema Owner (Doc ID 761636.1)

Last updated on SEPTEMBER 25, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Symptoms

  • As UserA registered an XML Schema and an XMLTYPE table created based upon this XML Schema.

    BEGIN 
      DBMS_XMLSCHEMA.registerschema('emp.xsd', 
    '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
                xmlns:xdb="http://xmlns.oracle.com/xdb"> 
      <xs:element name="Employee" xdb:SQLType="EMP_TYPE"> 
        <xs:complexType> 
          <xs:sequence> 
            <xs:element name="EmployeeId" type="xs:positiveInteger"/> 
            <xs:element name="PhoneNumber" maxOccurs="10"> 
             <xs:complexType> 
              <xs:attribute name="No" type="xs:integer"/> 
             </xs:complexType> 
            </xs:element> 
          </xs:sequence> 
        </xs:complexType> 
      </xs:element> 
    </xs:schema>', 
        local     => TRUE, 
        genTypes  => TRUE, 
        genBean   => FALSE, 
        genTables => FALSE 
      ); 

    END; 


    CREATE table emp_tab OF XMLType 
    XMLSCHEMA "emp.xsd" ELEMENT "Employee" 
    VARRAY xmldata."PhoneNumber" STORE AS table phone_tab; 

  • Privileges granted to UserB.

    GRANT all ON emp_tab TO UserB;

  • UserB fails to insert an XML document into the Schema-Based table.
      
    INSERT INTO UserA.emp_tab
    VALUES(XMLType('<Employee>
    <EmployeeId>1234</EmployeeId>
    <PhoneNumber No="1234"/>
    <PhoneNumber No="2345"/>
    </Employee>').createschemabasedxml('emp.xsd')); 
        
    ERROR at line 6:
    ORA-31000: Resource 'emp.xsd' is not an XDB schema document
    ORA-06512: at "SYS.XMLTYPE", line 232
    ORA-06512: at line 1

     

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