4 Replies Latest reply: Nov 27, 2012 5:13 AM by 933668 RSS

    error when registering an XML schema

    933668
      I get the following error:

      ORA-31084: error while creating table PC"."PCT_XML_CUF" for element "CUF""
      ORA-02320: failure in creating storage table for nested table column "XMLDATA"."SORTEERCODES"
      ORA-01031: insufficient privileges
      ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 72
      ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 33
      ORA-06512: at line 14
      ORA-06512: at line 3
      ORA-06512: at "SYS.DBMS_SQL", line 1825
      ORA-06512: at "ISAR1.PAISA_INSTALLER", line 229
      ORA-06512: at "ISAR1.PAISA_INSTALLER", line 281

      This is done by executing the following statement using DBMS_SQL.

      BEGIN

      EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA=PC';

      DECLARE
      v_schema CLOB;
      BEGIN
      PAISA_CLOB.load_from_db(i_clb_id => 31,
      io_clob => v_schema);

      BEGIN
      DBMS_XMLSCHEMA.deleteschema('cufSchema', DBMS_XMLSCHEMA.DELETE_CASCADE_FORCE);
      EXCEPTION
      WHEN others THEN null;
      END;

      DBMS_XMLSCHEMA.registerschema('cufSchema', v_schema, true);
      END;

      END;


      The package PAISA_INSTALLER is located in a central schema ISAR1.
      User PC calls ISAR1.PAISA_INSTALLER and the code should be executed in PC schema. That's why an alter session is called first.

      Do I need extra grants to execute this using dynamic SQL ?
      When executing it directly in the PC schema using sql*plus (without dynamic sql), the XML schema gets registered fine.
      But I need this to be dynamic cause we want to be able to repeat the installatation after export / import from a production to a test database.
      After import, the XML schemas are invalid. Repeating the installating fixes this.

      select grantee, PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE IN ('PC', 'ISAR1', 'XDB') AND PRIVILEGE LIKE 'CREATE%' ORDER BY grantee, PRIVILEGE

      ISAR1     CREATE ANY PROCEDURE
      ISAR1     CREATE ANY SYNONYM
      ISAR1     CREATE ANY TABLE
      ISAR1     CREATE ANY TRIGGER
      ISAR1     CREATE ANY TYPE
      ISAR1     CREATE ANY VIEW
      ISAR1     CREATE PROCEDURE
      ISAR1     CREATE ROLE
      ISAR1     CREATE ROLLBACK SEGMENT
      ISAR1     CREATE SESSION
      ISAR1     CREATE TRIGGER
      ISAR1     CREATE TYPE
      ISAR1     CREATE USER
      PC     CREATE ANY MATERIALIZED VIEW
      PC     CREATE ANY TABLE
      PC     CREATE ANY VIEW
      PC     CREATE PROCEDURE
      PC     CREATE PUBLIC SYNONYM
      PC     CREATE ROLE
      PC     CREATE SEQUENCE
      PC     CREATE SESSION
      PC     CREATE TABLE
      PC     CREATE TRIGGER
      PC     CREATE TYPE
      PC     CREATE USER
      PC     CREATE VIEW
      XDB     CREATE ANY TABLE
      XDB     CREATE ANY TYPE
      XDB     CREATE INDEXTYPE
      XDB     CREATE LIBRARY
      XDB     CREATE OPERATOR
      XDB     CREATE PUBLIC SYNONYM
      XDB     CREATE SESSION
      XDB     CREATE VIEW
        • 1. Re: error when registering an XML schema
          odie_63
          But I need this to be dynamic cause we want to be able to repeat the installatation after export / import from a production to a test database.
          Sorry, I don't see the connection.

          In this specific case, what does dynamic PL/SQL offer that a static PL/SQL block doesn't?
          • 2. Re: error when registering an XML schema
            933668
            Reinstalling should be easily repeatable, without using sql*plus script.

            I want to be able to do this with a simple statement. (PAISA_INSTALLER.install...)
            The code for registering the xml schema can differ with each version of the application.The statement (PAISA_INSTALLER.install...) will always remain the same.
            The tool we use for export / import just has to know what statement it has to call to reinstall it.

            The PAISA_INSTALLER package is a generic piece of code.
            The code to install the xml schema is located as text in a table.
            When installing the newest version of the application, the installation script is uploaded in the database.
            Afterwards, after copying to a test database, the code located in the table is executed again.
            • 3. Re: error when registering an XML schema
              odie_63
              User PC calls ISAR1.PAISA_INSTALLER and the code should be executed in PC schema. That's why an alter session is called first.
              If PC is the session user then CURRENT_SCHEMA is already set to PC, you don't need the ALTER SESSION.

              Try adding the AUTHID CURRENT_USER property to the install procedure.

              Following scenario works for me :

              1) Creating a new user with CONNECT role, CREATE TABLE and CREATE TYPE privileges :
              C:\Users\Marc>sqlplus /nolog
              
              SQL*Plus: Release 11.2.0.2.0 Production on Lun. Nov. 26 20:48:09 2012
              
              Copyright (c) 1982, 2010, Oracle.  All rights reserved.
              
              SQL> conn dev@xe
              Enter password:
              Connected.
              SQL> create user otn identified by otn
                2  default tablespace users
                3  quota unlimited on users;
              
              User created.
              
              SQL> grant connect to otn;
              
              Grant succeeded.
              
              SQL> grant create table to otn;
              
              Grant succeeded.
              
              SQL> grant create type to otn;
              
              Grant succeeded.
              2) Creating the install procedure in the main schema "DEV" :
              SQL> CREATE OR REPLACE PROCEDURE install
                2  IS
                3  BEGIN
                4
                5   execute immediate q'{DECLARE
                6    v_schema CLOB :=
                7  '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
                8    <xs:complexType name="worksheetType" xdb:SQLType="T_WORKSHEET">
                9      <xs:attribute name="sheetName" type="xs:string"/>
               10      <xs:attribute name="sheetId" type="xs:integer"/>
               11    </xs:complexType>
               12    <xs:complexType name="workbookType" xdb:SQLType="T_WORKBOOK" xdb:maintainDOM="false">
               13      <xs:sequence>
               14        <xs:element name="worksheet" type="worksheetType" minOccurs="1" maxOccurs="unbounded" xdb:SQLCollType="T_WORKSHEET_COLL"/>
               15      </xs:sequence>
               16    </xs:complexType>
               17    <xs:element name="workbook" type="workbookType" xdb:defaultTable="WORKBOOK_XML"/>
               18  </xs:schema>';
               19
               20  BEGIN
               21
               22    BEGIN
               23      DBMS_XMLSCHEMA.deleteschema('workbook.xsd', DBMS_XMLSCHEMA.DELETE_CASCADE);
               24    EXCEPTION
               25      WHEN others THEN null;
               26    END;
               27
               28    dbms_xmlschema.registerSchema(
               29      schemaURL => 'workbook.xsd'
               30    , schemaDoc => v_schema
               31    , local => true
               32    , genTypes => true
               33    , genTables => true
               34    , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
               35    );
               36
               37  END;}';
               38
               39  END;
               40  /
              
              Procedure created.
              
              SQL> grant execute on install to otn;
              
              Grant succeeded.
              
              SQL> disconn
              Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
              3) Testing the install call from OTN schema :
              SQL> conn otn/otn@xe
              Connected.
              SQL> show user
              USER is "OTN"
              SQL> exec dev.install
              BEGIN dev.install; END;
              
              *
              ERROR at line 1:
              ORA-01031: insufficient privileges
              ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 72
              ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 33
              ORA-06512: at line 24
              ORA-06512: at "DEV.INSTALL", line 5
              ORA-06512: at line 1
              
              
              SQL> disconn
              Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
              4) Let's add the AUTHID property and try again :
              SQL> conn dev@xe
              Enter password:
              Connected.
              SQL> CREATE OR REPLACE PROCEDURE install
                2  AUTHID CURRENT_USER
                3  IS
                4  BEGIN
                5
                6   execute immediate q'{DECLARE
                7    v_schema CLOB :=
                8  '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb">
                9    <xs:complexType name="worksheetType" xdb:SQLType="T_WORKSHEET">
               10      <xs:attribute name="sheetName" type="xs:string"/>
               11      <xs:attribute name="sheetId" type="xs:integer"/>
               12    </xs:complexType>
               13    <xs:complexType name="workbookType" xdb:SQLType="T_WORKBOOK" xdb:maintainDOM="false">
               14      <xs:sequence>
               15        <xs:element name="worksheet" type="worksheetType" minOccurs="1" maxOccurs="unbounded" xdb:SQLCollType="T_WORKSHEET_COLL"/>
               16      </xs:sequence>
               17    </xs:complexType>
               18    <xs:element name="workbook" type="workbookType" xdb:defaultTable="WORKBOOK_XML"/>
               19  </xs:schema>';
               20
               21  BEGIN
               22
               23    BEGIN
               24      DBMS_XMLSCHEMA.deleteschema('workbook.xsd', DBMS_XMLSCHEMA.DELETE_CASCADE);
               25    EXCEPTION
               26      WHEN others THEN null;
               27    END;
               28
               29    dbms_xmlschema.registerSchema(
               30      schemaURL => 'workbook.xsd'
               31    , schemaDoc => v_schema
               32    , local => true
               33    , genTypes => true
               34    , genTables => true
               35    , enableHierarchy => dbms_xmlschema.ENABLE_HIERARCHY_NONE
               36    );
               37
               38  END;}';
               39
               40  END;
               41  /
              
              Procedure created.
              
              SQL> disconn
              Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
              SQL> conn otn/otn@xe
              Connected.
              SQL> exec dev.install
              
              PL/SQL procedure successfully completed.
              • 4. Re: error when registering an XML schema
                933668
                Tnx, that worked.

                CREATE OR REPLACE PACKAGE paisa_installer AUTHID CURRENT_USER
                IS
                ...