This discussion is archived
4 Replies Latest reply: Nov 27, 2012 3:13 AM by 933668 RSS

error when registering an XML schema

933668 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Tnx, that worked.

    CREATE OR REPLACE PACKAGE paisa_installer AUTHID CURRENT_USER
    IS
    ...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points