9 Replies Latest reply: Jun 25, 2012 8:32 AM by 784111 RSS

    error loading sample xsd file into database

    784111
      SQL> select * from v$version ;

      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      PL/SQL Release 11.1.0.7.0 - Production
      CORE 11.1.0.7.0 Production
      TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
      NLSRTL Version 11.1.0.7.0 - Production
      -----------

      I am trying to load a simple xsd into the database to see how things work and expand on that for what I really need. I am connected as "SYS as sysdba".

      I used two xsd documents that can be found on this page http://www.oracle-base.com/articles/9i/xmldb-9i.php

      I am using two files referenced in the above link

      ----------------

      http://www.oracle-base.com/articles/9i/basic_message.xsd

      <?xml version="1.0" ?>
      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:element name="message" type="messageType" />
      <xs:complexType name="messageType">
      <xs:sequence>
      <xs:element name="to" type="xs:string" />
      <xs:element name="from" type="xs:string" />
      <xs:element name="subject" type="xs:string" />
      <xs:element name="body" type="xs:string" />
      </xs:sequence>
      </xs:complexType>
      </xs:schema>

      ---------

      and

      -----------

      http://www.oracle-base.com/articles/9i/message.xsd

      <?xml version="1.0" ?>
      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
      <xs:element name="message" type="messageType" xdb:defaultTable="MESSAGES" />
      <xs:complexType name="messageType" xdb:SQLType="MESSAGE_TYPE">
      <xs:sequence>
      <xs:element name="to" type="xs:string" />
      <xs:element name="from" type="xs:string" />
      <xs:element name="subject" type="xs:string" />
      <xs:element name="body" type="xs:string" />
      </xs:sequence>
      </xs:complexType>
      </xs:schema>

      ---------------

      When I try the following code using the contents of besic_message.xsd

      BEGIN
      DBMS_XMLSchema.registerSchema(
      schemaurl=>'http://localhost:8080/public/message.xsd',
      schemadoc=>'<?xml version="1.0" ?>
      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
      <xs:element name="message" type="messageType" />
      <xs:complexType name="messageType">
      <xs:sequence>
      <xs:element name="to" type="xs:string" />
      <xs:element name="from" type="xs:string" />
      <xs:element name="subject" type="xs:string" />
      <xs:element name="body" type="xs:string" />
      </xs:sequence>
      </xs:complexType>
      </xs:schema>'
      );
      END;
      /


      I get the error

      error at line 2
      ora-31154: invalid xml document
      ora-19202: error occurred in xml parsing
      LSX-0020: unknown type "xs.string"
      ora-06512: at "xdb.dbms_xmlschema_int", line 3
      ora-06512: at "xdb.dbms_xmlschema", line 14
      ora-06512: at line 2

      ------------------

      When I try using the contents of message.xsd

      BEGIN
      DBMS_XMLSchema.registerSchema(
      schemaurl=>'http://localhost:8080/public/message.xsd',
      schemadoc=>'<?xml version="1.0" ?>
      <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
      <xs:element name="message" type="messageType" xdb:defaultTable="MESSAGES" />
      <xs:complexType name="messageType" xdb:SQLType="MESSAGE_TYPE">
      <xs:sequence>
      <xs:element name="to" type="xs:string" />
      <xs:element name="from" type="xs:string" />
      <xs:element name="subject" type="xs:string" />
      <xs:element name="body" type="xs:string" />
      </xs:sequence>
      </xs:complexType>
      </xs:schema>'
      );
      END;
      /

      I get the error

      ORA-30937: No schema definition for 'xdb.defaultTable(namespace '##local') in parent '/schema/element[1]'

      I get the ora-30937 error even if I try loading the file directly using Enterprise manager grid control.

      Any help would be appreciated.

      Thanks in advance,

      Wally
        • 1. Re: error loading sample xsd file into database
          odie_63
          Hi,
          I am connected as "SYS as sysdba".
          Bad idea.
          I don't know if that's directly related to the errors but anyway don't create/register anything in SYS schema.
          Create your own schema, grant the necessary privileges, but don't use SYS.
          • 2. Re: error loading sample xsd file into database
            784111
            I will try that and let you know how it goes Odie.
            • 3. Re: error loading sample xsd file into database
              784111
              Odie,

              I created a new user with DBA, and XDBADMIN roles granted. When I ran both the pl/sql blocks above the above code, I get the same errors.

              I did try something different both as SYS and as the new user I created. Regardless of user, in in both the pl/sql blocks I added more parameters and got a different error. I have shortened the code for brevity.

              BEGIN
              DBMS_XMLSchema.registerSchema(
              schemaurl=>'http://localhost:8080/public/message.xsd',
              schemadoc=>'<?xml version="1.0" ?>
              ............................
              <xs:element name="to" type="xs:string" />
              <xs:element name="from" type="xs:string" />
              <xs:element name="subject" type="xs:string" />
              <xs:element name="body" type="xs:string" />
              </xs:sequence>
              </xs:complexType>
              </xs:schema>',
              local=> TRUE,
              gentypes=> true,
              genbean=> false,
              gentables=> false,
              force=> false,
              owner=> null,
              enablehierarchy=> 2,
              options=> 0
              );
              END;
              /

              This time I get a single error

              ora-01405: fetched column values are null

              Thanks for your help.

              Wally
              • 4. Re: error loading sample xsd file into database
                Jason_(A_Non)
                It doesn't like the line
                owner=> NULL,
                When that line is included, you are hitting Bug 9660167. If you take that line out, it should register for you, at least it did for me on 11.1.0.6, though I used
                SQL> 
                SQL> BEGIN
                  2  DBMS_XMLSchema.registerSchema(
                  3  schemaurl=>'message.xsd',
                  4  schemadoc=>'<?xml version="1.0"?>
                  5  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
                  6       <xs:element name="message" type="messageType" xdb:defaultTable="MESSAGES" />
                  7       <xs:complexType name="messageType" xdb:SQLType="MESSAGE_TYPE">
                  8      <xs:sequence>
                  9        <xs:element name="to" type="xs:string"/>
                 10        <xs:element name="from" type="xs:string"/>
                 11        <xs:element name="subject" type="xs:string"/>
                 12        <xs:element name="body" type="xs:string"/>
                 13      </xs:sequence>
                 14    </xs:complexType>
                 15  </xs:schema>',
                 16  local=> TRUE,
                 17  gentypes=> true,
                 18  genbean=> false,
                 19  gentables=> false,
                 20  enablehierarchy=> dbms_xmlschema.ENABLE_HIERARCHY_CONTENTS);
                 21  END;
                 22  /
                 
                PL/SQL procedure successfully completed
                Yes that is the default value per the spec, but who knows why it is happening.
                • 5. Re: error loading sample xsd file into database
                  784111
                  Thanks A Non.

                  I will try that and respond back.
                  • 6. Re: error loading sample xsd file into database
                    784111
                    I tried the code exactly the way you had it and I got the same error as before

                    ORA-30937: No schema definition for 'xdb.defaultTable(namespace '##local') in parent '/schema/element[1]'
                    ora-06512: at "xdb.dbms_xmlschema_int", line 3
                    ora-06512: at "xdb.dbms_xmlschema", line 14
                    ora-06512: at line 2


                    I tried the basic_message.xml file with the same parameters as you and I still get the error at line 2

                    ora-31154: invalid xml document
                    ora-19202: error occurred in xml parsing
                    LSX-0020: unknown type "xs.string"
                    ora-06512: at "xdb.dbms_xmlschema_int", line 3
                    ora-06512: at "xdb.dbms_xmlschema", line 14
                    ora-06512: at line 2

                    Is there something I have to setup in XDB to make things work ? I am able to go to http://localhost:8080 and I can access the folders and files in the site.

                    Thanks again.
                    • 7. Re: error loading sample xsd file into database
                      Jason_(A_Non)
                      I took the basic_message.xsd from the above link and was able to register it successfully
                      SQL> BEGIN
                        2  DBMS_XMLSchema.registerSchema(
                        3  schemaurl=>'message.xsd',
                        4  schemadoc=>'<?xml version="1.0"?>
                        5  <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
                        6       <xs:element name="message" type="messageType"/>
                        7       <xs:complexType name="messageType">
                        8      <xs:sequence>
                        9        <xs:element name="to" type="xs:string"/>
                       10        <xs:element name="from" type="xs:string"/>
                       11        <xs:element name="subject" type="xs:string"/>
                       12        <xs:element name="body" type="xs:string"/>
                       13      </xs:sequence>
                       14    </xs:complexType>
                       15  </xs:schema>',
                       16  local=> TRUE,
                       17  gentypes=> true,
                       18  genbean=> false,
                       19  gentables=> false,
                       20  enablehierarchy=> dbms_xmlschema.ENABLE_HIERARCHY_CONTENTS);
                       21  END;
                       22  /
                       
                      PL/SQL procedure successfully completed
                      In your error for this registration, the following error bothers me
                      LSX-0020: unknown type "xs.string"
                      There is no xs.string in the above schema, so the question is ... where is it coming from?

                      Please copy what you are running and paste it into the forums. Please use the code tags as in
                      {<tt>code} {code</tt>}
                      (taken from the FAQ page)
                      so that we can see for certain what is going on.
                      • 8. Re: error loading sample xsd file into database
                        784111
                        Odie,

                        I fixed the problem. After you recommended not to use the SYS account, I created an account to register the XSDs. I created an external user in the database with the DBA and XDBADMIN roles. The thing was, this external user's username was of the format DOMAINNAME\DOMAINUSERNAME. Assume that my windows Domain is called Oracle and my username is Wally, my Oracle external username is "ORACLE\WALLY" with the slash included. The STIG requirement on our project says that the domain name is to be included in the username.

                        I then created a regular user and granted the DBA and XDBADMIN grants. The code I included in my original post worked like a charm. I opened an SR with Oracle support and they said they will see if they can replicate the issue with the external user that has a \ in the username. Since the STIG requirement forces a \ in the username I couldn't test an external user without a \ in the username. I want to know if the issue is with the \ or with all external users.

                        I hope this is a bug and not something that only I am experiencing.

                        Thanks again for your and everyone's suggestion in this thread.

                        Wally
                        • 9. Re: error loading sample xsd file into database
                          784111
                          For those of you keeping track, Oracle Support created a bug report for this issue since they were able to recreate the issues I had.


                          Bug 14237864 - ORA-31020 REGISTERNG AN XML SCHEMA AS AN EXTERNALLY IDENTIFIED USER ON WINDOWS