This discussion is archived
5 Replies Latest reply: Nov 6, 2012 9:44 AM by MarcoGralike RSS

Unable to register XSD into Oracle using trigger / procedure

972532 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    Your welcome.

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

Legend

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