5 Replies Latest reply: Nov 6, 2012 11:44 AM by Marco Gralike RSS

    Unable to register XSD into Oracle using trigger / procedure

    972532
      Hi,

      I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production.

      I have a table which stores XSD and i need to register this XSD. I am able to register the XSD if dbms_schema is executed from PLSQL anonymous block. But if i mention the same code in trigger / procedure is not working . Getting the error as insufficient privileges.

      Table, trigger and procedure are in same schema. This schema has all the privs required for XSD registration globally.

      create table t_vkk ( n number, xsd xmltype)

      alter table t_vkk add active varchar2(1)

      create sequence seq_vkk start with 1 increment by 1

      CREATE OR REPLACE TRIGGER TRG_t_vkk_xsd
      AFTER INSERT OR UPDATE
      ON t_vkk
      REFERENCING OLD AS old NEW AS new
      FOR EACH ROW
      BEGIN
      IF INSERTING AND :new.active = 'Y'
      THEN
      prc_reg_xsd(:new.n, :new.xsd);
      END IF;
      -- Applied when updating rows in the table
      IF UPDATING
      THEN
      IF :new.active = 'Y' -- XSD re-registration since template id is still active
      THEN
      prc_reg_xsd(:old.n, :old.xsd);
      ELSIF :new.active = 'N' -- XSD de-registration
      THEN
      BEGIN
      dbms_xmlschema.deleteschema (
      schemaurl => :old.n
      , delete_option => dbms_xmlschema.delete_cascade_force
      );
      EXCEPTION
      WHEN OTHERS
      THEN
      NULL; -- This XSD was not registered
      END;
      END IF;
      END IF;
      END TRG_t_vkk_xsd;

      This fails -
      insert into t_vkk
      values (0,XMLTYPE(
      '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:books" xmlns:bks="urn:books">
      <xsd:element name="books" type="bks:BooksForm"/>
      <xsd:complexType name="BooksForm">
      <xsd:sequence>
      <xsd:element name="book" type="bks:BookForm" minOccurs="0" maxOccurs="unbounded"/>
      </xsd:sequence>
      </xsd:complexType>
      <xsd:complexType name="BookForm">
      <xsd:sequence>
      <xsd:element name="author" type="xsd:string"/>
      <xsd:element name="title" type="xsd:string"/>
      <xsd:element name="genre" type="xsd:string"/>
      <xsd:element name="price" type="xsd:float"/>
      <xsd:element name="pub_date" type="xsd:date"/>
      <xsd:element name="review" type="xsd:string"/>
      </xsd:sequence>
      <xsd:attribute name="id" type="xsd:string"/>
      </xsd:complexType>
      </xsd:schema>'
      ), 'Y')

      This works :
      After inserting the record into t_vkk (Trigger is disabled)
      declare
      begin
      for x in (select * from t_vkk)
      loop
      dbms_output.put_line (x.xsd.getstringval());
      BEGIN
      dbms_xmlschema.deleteschema (
      schemaurl => x.n
      , delete_option => dbms_xmlschema.delete_cascade_force
      );
      EXCEPTION
      WHEN OTHERS
      THEN
      NULL; -- This XSD was not registered
      END;

      dbms_xmlschema.registerschema (schemaurl => x.n
      , schemadoc => x.xsd
      , local => false);
      end loop;
      end;

      This Fails:
      create or replace procedure prc_reg_xsd
      as
      begin
      for x IN (select * from t_vkk)
      loop
      BEGIN
      dbms_xmlschema.deleteschema (
      schemaurl => x.n
      , delete_option => dbms_xmlschema.delete_cascade_force
      );
      EXCEPTION
      WHEN OTHERS
      THEN
      NULL; -- This XSD was not registered
      END;
      dbms_xmlschema.registerschema (schemaurl => x.n
      , schemadoc => x.xsd
      , local => false);
      end loop;
      end;

      BEGIN
      prc_reg_xsd;
      END;

      -Vinod K
        • 1. Re: Unable to register XSD into Oracle using trigger / procedure
          odie_63
          Hi,

          Two things :

          - The required privileges have to be granted directly to the user (not through roles) for the registration to work in stored program units. It's a general rule, nothing specific to XMLDB.

          - Are you using object-relational storage or just the schema validation feature? If the latter, at least register the schemas with genTypes and genTables parameters set to "false".
          The exception handling could be better too : only handle exceptions you expect.
          • 2. Re: Unable to register XSD into Oracle using trigger / procedure
            MGralike
            Guessing here that your problem is introduced by a typical trigger issue(s). The DBMS_XMLSCHEMA bit will explicitly enforce the commit. This is not allowed in a trigger. Another issue is that you will have to explicitly enforce a schema that has the proper privileges and is allowed to register the XML schema. So my guess is that it currently fails on the exact privileged bit (INVOKER contra DEFINER rights in PL/SQL: http://www.stanford.edu/dept/itss/docs/oracle/10g/network.101/b10773/authoriz.htm#1006600). When you have solved this one, you will probably encounter the commit/rollback issue related to triggers...
            • 3. Re: Unable to register XSD into Oracle using trigger / procedure
              odie_63
              The DBMS_XMLSCHEMA bit will explicitly enforce the commit. This is not allowed in a trigger.
              Very good point.
              • 4. Re: Unable to register XSD into Oracle using trigger / procedure
                972532
                Thanks everyone. I got the solution from one of my colleague. I can invoke dbms_xmlschema.registerschema through my standalone procedure by using current_user authid.

                create or replace procedure prc_reg_xsd
                (in_id IN NUMBER
                , in_xsd IN XMLTYPE)
                authid current_user
                as
                begin
                dbms_output.put_line (' startd');
                BEGIN
                dbms_xmlschema.deleteschema (
                schemaurl => in_id
                , delete_option => dbms_xmlschema.delete_cascade_force
                );
                EXCEPTION
                WHEN OTHERS
                THEN
                dbms_output.put_line (' Failed'); -- This XSD was not registered
                END;

                dbms_xmlschema.registerschema (schemaurl => in_id
                , schemadoc => in_xsd
                , local => false);
                dbms_output.put_line (' done');
                end;

                DECLARE
                x number:=9;
                t xmltype := XMLTYPE(
                '<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="urn:books" xmlns:bks="urn:books">
                <xsd:element name="books" type="bks:BooksForm"/>
                <xsd:complexType name="BooksForm">
                <xsd:sequence>
                <xsd:element name="book" type="bks:BookForm" minOccurs="0" maxOccurs="unbounded"/>
                </xsd:sequence>
                </xsd:complexType>
                <xsd:complexType name="BookForm">
                <xsd:sequence>
                <xsd:element name="author" type="xsd:string"/>
                <xsd:element name="title" type="xsd:string"/>
                <xsd:element name="genre" type="xsd:string"/>
                <xsd:element name="price" type="xsd:float"/>
                <xsd:element name="pub_date" type="xsd:date"/>
                <xsd:element name="review" type="xsd:string"/>
                </xsd:sequence>
                <xsd:attribute name="id" type="xsd:string"/>
                </xsd:complexType>
                </xsd:schema>');
                BEGIN
                prc_reg_xsd(x,t);
                END;

                This worked for me. The procedure is in the same schema which has XDBADMIN role. So i am able to register the XSD.

                Vinod
                • 5. Re: Unable to register XSD into Oracle using trigger / procedure
                  Marco Gralike
                  Your welcome.

                  ...but does this work being called from the trigger as well...?