6 Replies Latest reply on Dec 7, 2010 10:24 AM by ups_mike

    XMLTYPE schemaValidate() Problem...

    ups_mike
      Hi everybody!

      I have a problem using the procedure XMLTYPE.schemaValidate();
      Can anyone help me with this issue?

      I always get the error: ORA-30937: No schema definition for 'COMPONENT_ID' (namespace '##local') in parent '/P_WAFERLIST/WAFER[1]'


      Here is what I have done:
      _1) First I created the following schema definition:_
      <?xml version="1.0"?>
      <xs:schema id="P_WAFERLIST" targetNamespace="http://portal.xxx.com/xsd_schemadefinitions/ecf/prop" xmlns:xs="http://www.w3.org/2001/XMLSchema" attributeFormDefault="qualified" elementFormDefault="qualified">
        <xs:element name="P_WAFERLIST">
          <xs:complexType>
            <xs:choice minOccurs="0" maxOccurs="unbounded">
              <xs:element name="WAFER">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="PARAMETERS" minOccurs="0" maxOccurs="unbounded">
                      <xs:complexType>
                        <xs:sequence>
                          <xs:element name="PARAMETER" nillable="true" minOccurs="0" maxOccurs="unbounded">
                            <xs:complexType>
                              <xs:simpleContent>
                                <xs:extension base="xs:string">
                                  <xs:attribute name="NAME" form="unqualified" type="xs:string" />
                                </xs:extension>
                              </xs:simpleContent>
                            </xs:complexType>
                          </xs:element>
                        </xs:sequence>
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                  <xs:attribute name="COMPONENT_ID" form="unqualified" type="xs:string" />
                  <xs:attribute name="SUBQTY" form="unqualified" type="xs:string" />
                  <xs:attribute name="STATE" form="unqualified" type="xs:string" />
                </xs:complexType>
              </xs:element>
            </xs:choice>
          </xs:complexType>
        </xs:element>
      </xs:schema>
      _2) Next I performed a DBMS_XMLSCHEMA.registerSchema_
            DBMS_XMLSCHEMA.registerSchema ('http://portal.xxx.com/xsd_schemadefinitions/ecf/prop',
                                           sys.UriFactory.getUri ('http://portal.xxx.com/xsd_schemadefinitions/ecf/prop/ecf_property.xsd'),
                                           TRUE,
                                           TRUE,
                                           FALSE);
      _3) This is a sample XML File to reproduce the error:_
      <P_WAFERLIST xmlns="http://portal.xxx.com/xsd_schemadefinitions/ecf/prop">
        <WAFER COMPONENT_ID="C11026W01PE6" SUBQTY="22500" STATE="SPLIT">
          <PARAMETERS>
            <PARAMETER NAME="PARAMETER">PARA1_VALUE</PARAMETER>
            <PARAMETER NAME="PARAMETER">PARA2_VALUE</PARAMETER>
            <PARAMETER NAME="PARAMETER">PARA3_VALUE</PARAMETER>
          </PARAMETERS>
        </WAFER>
        <WAFER COMPONENT_ID="C11026W02PH1" SUBQTY="21700" STATE="SPLIT">
          <PARAMETERS>
            <PARAMETER NAME="PARAMETER">PARA1_VALUE</PARAMETER>
            <PARAMETER NAME="PARAMETER">PARA2_VALUE</PARAMETER>
            <PARAMETER NAME="PARAMETER">PARA3_VALUE</PARAMETER>
          </PARAMETERS>
        </WAFER>
        <WAFER COMPONENT_ID="C11026W05PG7" SUBQTY="24300" STATE="PRESENT">
          <PARAMETERS>
            <PARAMETER NAME="PARAMETER">PARA1_VALUE</PARAMETER>
            <PARAMETER NAME="PARAMETER">PARA2_VALUE</PARAMETER>
            <PARAMETER NAME="PARAMETER">PARA3_VALUE</PARAMETER>
          </PARAMETERS>
        </WAFER>
      </P_WAFERLIST>
      _4) And this is a code sample to reproduce the error:_
               //P_XML is of type XMLTYPE and contains the sample XML 
               l_info := P_XML.isSchemaValid ('http://portal.xxx.com/xsd_schemadefinitions/ecf/prop', 'P_WAFERLIST');
               //l_info = 1 --> as far as good...
               P_XML := P_XML.createSchemaBasedXML ('http://portal.xxx.com/xsd_schemadefinitions/ecf/prop');
               //No error until now
               P_XML.schemaValidate();
               //Performing the schema validation causes the error:
               //ORA-30937: No schema definition for 'COMPONENT_ID' (namespace '##local') in parent '/P_WAFERLIST/WAFER[1]'
      I really have no idea what is wrong and why the schemaValidate() procedure causes an error :-( Database version is 10g

      I would be grateful for any help!
      BR Mike

      Edited by: ups_mike on Dec 3, 2010 5:07 AM

      Edited by: mdrake on Dec 3, 2010 8:05 AM
      Add formatting tags.. Please try and format examples in future
        • 1. Re: XMLTYPE schemaValidate() Problem...
          mdrake-Oracle
          Testing in 11.2.0.2.0 I get
          SQL> connect sys/oracle as sysdba
          Connected.
          SQL> --
          SQL> set define on
          SQL> set timing on
          SQL> --
          SQL> def USERNAME = XDBTEST
          SQL> --
          SQL> def PASSWORD = &USERNAME
          SQL> --
          SQL> -- def XMLDIR = &1
          SQL> --
          SQL> def USER_TABLESPACE = USERS
          SQL> --
          SQL> def TEMP_TABLESPACE = TEMP
          SQL> --
          SQL> drop user &USERNAME cascade
            2  /
          old   1: drop user &USERNAME cascade
          new   1: drop user XDBTEST cascade
          
          User dropped.
          
          Elapsed: 00:00:00.29
          SQL> grant create any directory, drop any directory, connect, resource, alter session, create view to &USERNAME identified by &PASSWORD
            2  /
          old   1: grant create any directory, drop any directory, connect, resource, alter session, create view to &USERNAME identified by &PASSWORD
          new   1: grant create any directory, drop any directory, connect, resource, alter session, create view to XDBTEST identified by XDBTEST
          
          Grant succeeded.
          
          Elapsed: 00:00:00.03
          SQL> alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespace &TEMP_TABLESPACE
            2  /
          old   1: alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespace &TEMP_TABLESPACE
          new   1: alter user XDBTEST default tablespace USERS temporary tablespace TEMP
          
          User altered.
          
          Elapsed: 00:00:00.00
          SQL> connect &USERNAME/&PASSWORD
          Connected.
          SQL> --
          SQL> -- create or replace directory XMLDIR as '&XMLDIR'
          SQL> -- /
          SQL> var SCHEMAURL1    varchar2(256)
          SQL> VAR XMLSCHEMA1    CLOB;
          SQL> VAR INSTANCE1     CLOB;
          SQL> --
          SQL> set define off
          SQL> --
          SQL> alter session set events='31098 trace name context forever'
            2  /
          
          Session altered.
          
          Elapsed: 00:00:00.00
          SQL> begin
            2    :SCHEMAURL1:= 'http://portal.xxx.com/xsd_schemadefinitions/ecf/prop';
            3    :XMLSCHEMA1 :=
            4  '<?xml version="1.0"?>
            5  <xs:schema id="P_WAFERLIST" targetNamespace="http://portal.xxx.com/xsd_schemadefinitions/ecf/prop" xmlns:xs="http://www.w3.org/2001/XMLSchema" attributeFormDefault="qualified" elementFormDefault="qualified">
            6    <xs:element name="P_WAFERLIST">
            7      <xs:complexType>
            8        <xs:choice minOccurs="0" maxOccurs="unbounded">
            9          <xs:element name="WAFER">
           10            <xs:complexType>
           11              <xs:sequence>
           12                <xs:element name="PARAMETERS" minOccurs="0" maxOccurs="unbounded">
           13                  <xs:complexType>
           14                    <xs:sequence>
           15                      <xs:element name="PARAMETER" nillable="true" minOccurs="0" maxOccurs="unbounded">
           16                        <xs:complexType>
           17                          <xs:simpleContent>
           18                            <xs:extension base="xs:string">
           19                              <xs:attribute name="NAME" form="unqualified" type="xs:string" />
           20                            </xs:extension>
           21                          </xs:simpleContent>
           22                        </xs:complexType>
           23                      </xs:element>
           24                    </xs:sequence>
           25                  </xs:complexType>
           26                </xs:element>
           27              </xs:sequence>
           28              <xs:attribute name="COMPONENT_ID" form="unqualified" type="xs:string" />
           29              <xs:attribute name="SUBQTY" form="unqualified" type="xs:string" />
           30              <xs:attribute name="STATE" form="unqualified" type="xs:string" />
           31            </xs:complexType>
           32          </xs:element>
           33        </xs:choice>
           34      </xs:complexType>
           35    </xs:element>
           36  </xs:schema>';
           37    :INSTANCE1 :=
           38  '<P_WAFERLIST xmlns="http://portal.xxx.com/xsd_schemadefinitions/ecf/prop">
           39    <WAFER COMPONENT_ID="C11026W01PE6" SUBQTY="22500" STATE="SPLIT">
           40      <PARAMETERS>
           41        <PARAMETER NAME="PARAMETER">PARA1_VALUE</PARAMETER>
           42        <PARAMETER NAME="PARAMETER">PARA2_VALUE</PARAMETER>
           43        <PARAMETER NAME="PARAMETER">PARA3_VALUE</PARAMETER>
           44      </PARAMETERS>
           45    </WAFER>
           46    <WAFER COMPONENT_ID="C11026W02PH1" SUBQTY="21700" STATE="SPLIT">
           47      <PARAMETERS>
           48        <PARAMETER NAME="PARAMETER">PARA1_VALUE</PARAMETER>
           49        <PARAMETER NAME="PARAMETER">PARA2_VALUE</PARAMETER>
           50        <PARAMETER NAME="PARAMETER">PARA3_VALUE</PARAMETER>
           51      </PARAMETERS>
           52    </WAFER>
           53    <WAFER COMPONENT_ID="C11026W05PG7" SUBQTY="24300" STATE="PRESENT">
           54      <PARAMETERS>
           55        <PARAMETER NAME="PARAMETER">PARA1_VALUE</PARAMETER>
           56        <PARAMETER NAME="PARAMETER">PARA2_VALUE</PARAMETER>
           57        <PARAMETER NAME="PARAMETER">PARA3_VALUE</PARAMETER>
           58      </PARAMETERS>
           59    </WAFER>
           60  </P_WAFERLIST>';
           61  end;
           62  /
          
          PL/SQL procedure successfully completed.
          
          Elapsed: 00:00:00.00
          SQL> set define on
          SQL> --
          SQL> declare
            2    V_XML_SCHEMA xmlType := XMLType(:XMLSCHEMA1);
            3  begin
            4    DBMS_XMLSCHEMA.registerSchema
            5    (
            6      SCHEMAURL        => :SCHEMAURL1,
            7      SCHEMADOC        => V_XML_SCHEMA,
            8      LOCAL            => TRUE,
            9      GENBEAN          => FALSE,
           10      GENTYPES         => TRUE,
           11      GENTABLES        => FALSE,
           12      ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
           13    );
           14  end;
           15  /
          
          PL/SQL procedure successfully completed.
          
          Elapsed: 00:00:00.19
          SQL> declare
            2    V_XML XMLTYPE := XMLType(:INSTANCE1).createSchemaBasedXML (:SCHEMAURL1);
            3  begin
            4    V_XML.schemaValidate();
            5  end;
            6  /
          
          PL/SQL procedure successfully completed.
          
          Elapsed: 00:00:00.09
          SQL>
          However in 10.2.0.4.0 I get
          SQL> declare
            2    V_XML XMLTYPE := XMLType(:INSTANCE1).createSchemaBasedXML (:SCHEMAURL1);
            3  begin
            4    V_XML.schemaValidate();
            5  end;
            6  /
          declare
          *
          ERROR at line 1:
          ORA-30937: No schema definition for 'COMPONENT_ID' (namespace '##local') in
          parent '/P_WAFERLIST/WAFER[1]'
          ORA-06512: at "SYS.XMLTYPE", line 345
          ORA-06512: at line 4
          
          
          Elapsed: 00:00:00.05
          SQL>
          Also, check in XMLSpy the instance is valid per the XML Schema so it looks like a bug in 10.2.x.. If upgrading to 11.2.x is not an option you'll need to contact MoS and open a TAR for this issue...
          1 person found this helpful
          • 2. Re: XMLTYPE schemaValidate() Problem...
            mdrake-Oracle
            If you change the setting of attributeFormDefault in the Schema this appears to avoid the bug. I don't know if this is a viaable work around in your case, however you are expliciy stating the qualificaiton on each attribute, so the default shouild not matter.
            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
            
            SQL> set echo on
            SQL> spool testcase.log
            SQL> --
            SQL> connect sys/oracle as sysdba
            Connected.
            SQL> --
            SQL> set define on
            SQL> set timing on
            SQL> --
            SQL> def USERNAME = XDBTEST
            SQL> --
            SQL> def PASSWORD = &USERNAME
            SQL> --
            SQL> -- def XMLDIR = &1
            SQL> --
            SQL> def USER_TABLESPACE = USERS
            SQL> --
            SQL> def TEMP_TABLESPACE = TEMP
            SQL> --
            SQL> drop user &USERNAME cascade
              2  /
            old   1: drop user &USERNAME cascade
            new   1: drop user XDBTEST cascade
            
            User dropped.
            
            Elapsed: 00:00:00.39
            SQL> grant create any directory, drop any directory, connect, resource, alter se
            ssion, create view to &USERNAME identified by &PASSWORD
              2  /
            old   1: grant create any directory, drop any directory, connect, resource, alte
            r session, create view to &USERNAME identified by &PASSWORD
            new   1: grant create any directory, drop any directory, connect, resource, alte
            r session, create view to XDBTEST identified by XDBTEST
            
            Grant succeeded.
            
            Elapsed: 00:00:00.03
            SQL> alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespa
            ce &TEMP_TABLESPACE
              2  /
            old   1: alter user &USERNAME default tablespace &USER_TABLESPACE temporary tabl
            espace &TEMP_TABLESPACE
            new   1: alter user XDBTEST default tablespace USERS temporary tablespace TEMP
            
            User altered.
            
            Elapsed: 00:00:00.00
            SQL> connect &USERNAME/&PASSWORD
            Connected.
            SQL> --
            SQL> -- create or replace directory XMLDIR as '&XMLDIR'
            SQL> -- /
            SQL> var SCHEMAURL1    varchar2(256)
            SQL> VAR XMLSCHEMA1    CLOB;
            SQL> VAR INSTANCE1     CLOB;
            SQL> --
            SQL> set define off
            SQL> --
            SQL> alter session set events='31098 trace name context forever'
              2  /
            
            Session altered.
            
            Elapsed: 00:00:00.00
            SQL> begin
              2    :SCHEMAURL1:= 'http://portal.xxx.com/xsd_schemadefinitions/ecf/prop';
              3    :XMLSCHEMA1 :=
              4  '<?xml version="1.0"?>
              5  <xs:schema id="P_WAFERLIST" targetNamespace="http://portal.xxx.com/xsd_schemadefinitions/ecf/prop" xmlns:xs="http://www.w3.org/2001/XMLSchema" attributeFormDefault="unqualified" elementFormDefault="qualified">
              6    <xs:element name="P_WAFERLIST">
              7      <xs:complexType>
              8        <xs:choice minOccurs="0" maxOccurs="unbounded">
              9          <xs:element name="WAFER">
             10            <xs:complexType>
             11              <xs:sequence>
             12                <xs:element name="PARAMETERS" minOccurs="0" maxOccurs="unbounded">
             13                  <xs:complexType>
             14                    <xs:sequence>
             15                      <xs:element name="PARAMETER" nillable="true" minOccurs="0" maxOccurs="unbounded">
             16                        <xs:complexType>
             17                          <xs:simpleContent>
             18                            <xs:extension base="xs:string">
             19                              <xs:attribute name="NAME" form="unqualified" type="xs:string" />
             20                            </xs:extension>
             21                          </xs:simpleContent>
             22                        </xs:complexType>
             23                      </xs:element>
             24                    </xs:sequence>
             25                  </xs:complexType>
             26                </xs:element>
             27              </xs:sequence>
             28              <xs:attribute name="COMPONENT_ID" form="unqualified" type="xs:string" />
             29              <xs:attribute name="SUBQTY" form="unqualified" type="xs:string" />
             30              <xs:attribute name="STATE" form="unqualified" type="xs:string"/>
             31            </xs:complexType>
             32          </xs:element>
             33        </xs:choice>
             34      </xs:complexType>
             35    </xs:element>
             36  </xs:schema>';
             37    :INSTANCE1 :=
             38  '<P_WAFERLIST xmlns="http://portal.xxx.com/xsd_schemadefinitions/ecf/prop">
            
             39    <WAFER COMPONENT_ID="C11026W01PE6" SUBQTY="22500" STATE="SPLIT">
             40      <PARAMETERS>
             41        <PARAMETER NAME="PARAMETER">PARA1_VALUE</PARAMETER>
             42        <PARAMETER NAME="PARAMETER">PARA2_VALUE</PARAMETER>
             43        <PARAMETER NAME="PARAMETER">PARA3_VALUE</PARAMETER>
             44      </PARAMETERS>
             45    </WAFER>
             46    <WAFER COMPONENT_ID="C11026W02PH1" SUBQTY="21700" STATE="SPLIT">
             47      <PARAMETERS>
             48        <PARAMETER NAME="PARAMETER">PARA1_VALUE</PARAMETER>
             49        <PARAMETER NAME="PARAMETER">PARA2_VALUE</PARAMETER>
             50        <PARAMETER NAME="PARAMETER">PARA3_VALUE</PARAMETER>
             51      </PARAMETERS>
             52    </WAFER>
             53    <WAFER COMPONENT_ID="C11026W05PG7" SUBQTY="24300" STATE="PRESENT">
             54      <PARAMETERS>
             55        <PARAMETER NAME="PARAMETER">PARA1_VALUE</PARAMETER>
             56        <PARAMETER NAME="PARAMETER">PARA2_VALUE</PARAMETER>
             57        <PARAMETER NAME="PARAMETER">PARA3_VALUE</PARAMETER>
             58      </PARAMETERS>
             59    </WAFER>
             60  </P_WAFERLIST>';
             61  end;
             62  /
            
            PL/SQL procedure successfully completed.
            
            Elapsed: 00:00:00.00
            SQL> set define on
            SQL> --
            SQL> declare
              2    V_XML_SCHEMA xmlType := XMLType(:XMLSCHEMA1);
              3  begin
              4    DBMS_XMLSCHEMA.registerSchema
              5    (
              6      SCHEMAURL        => :SCHEMAURL1,
              7      SCHEMADOC        => V_XML_SCHEMA,
              8      LOCAL            => TRUE,
              9      GENBEAN          => FALSE,
             10      GENTYPES         => TRUE,
             11      GENTABLES        => FALSE,
             12      ENABLEHIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE
             13    );
             14  end;
             15  /
            
            PL/SQL procedure successfully completed.
            
            Elapsed: 00:00:00.64
            SQL> declare
              2    V_XML XMLTYPE := XMLType(:INSTANCE1).createSchemaBasedXML (:SCHEMAURL1);
              3  begin
              4    V_XML.schemaValidate();
              5  end;
              6  /
            
            PL/SQL procedure successfully completed.
            
            Elapsed: 00:00:00.02
            SQL>
            Edited by: mdrake on Dec 3, 2010 8:18 AM
            • 3. Re: XMLTYPE schemaValidate() Problem...
              ups_mike
              Hi mdrake!

              First, thanks for your support! You lead me on the right track to identify the reason for the error.

              Upgrading to 11g is no option for me. Also changing the attributeFormDefault to unqualified is not the solution I prefer, because those attributes are very important for the further processing.

              Stating the qualificaiton on each attribute and removing the default definition also doesn't avoid the error.

              So the only thing I can do is to open a TAR and work with unqualified attributes until the problem is fixed, or do you have any other suggestions?

              BR and thanks!!
              Mike
              • 4. Re: XMLTYPE schemaValidate() Problem...
                mdrake-Oracle
                Sorry, I have no other suiggestions, since you are constrained to working with really old software. You will need to open a tar with support, report a new bug (they really don't like me opening bugs on legacy software if the issue is already fixed in later prodcution software) and request a patch. Given the that the bug is already fixed in later production software you'll need to be prepared to proiide a really strong justification as to why a patch is necessary. Good Luck...