George, I've confirmed the problem on Oracle 9.2 DB WinXP Pro. Have a look
at the bottom of page 3-23 in A96620-02.pdf from http://www.oracle.com. Here
Oracle implement a BEFORE INSERT trigger through a temporary variable. If
you rejig your PL/SQL this way it works. Why I don't know but htis should
move you along,
Regards
Hex.
CHANGE YOUR CODE FROM:
CREATE OR REPLACE TRIGGER trgBeforeUpdateEmployeeDetails
BEFORE INSERT ON tblEmployeeDetails
FOR EACH ROW
BEGIN
:NEW.EmployeeDetail.SCHEMAVALIDATE();
END;
/
TO:
CREATE OR REPLACE TRIGGER trgBeforeUpdateEmployeeDetails
BEFORE INSERT ON tblEmployeeDetails
FOR EACH ROW
DECLARE
xmldata XMLTYPE;
BEGIN
xmldata:=:NEW.EmployeeDetail;
XMLTYPE.SCHEMAVALIDATE(xmldata);
END;
/
USAGE
SQL> --Schema Valid Test
SQL> INSERT INTO tblEmployeeDetails(ID,EmployeeDetail)
2 VALUES(101,XMLTYPE('<?xml version="1.0"?>
3 <EmployeeDetail
4 xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance"
5
xsi:noNamespaceSchemaLocation="
http://www.companyname.com/EmployeeDetails"
>
6 <EmployeeID>100</EmployeeID>
7 <EmployeeName>test string</EmployeeName>
8 </EmployeeDetail>
9 ')
10 );
1 row created.
SQL> --Schema INVALID Test
SQL> INSERT INTO tblEmployeeDetails(ID,EmployeeDetail)
2 VALUES(102,XMLTYPE('<?xml version="1.0"?>
3 <EmployeeDetail
4 xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance"
5
xsi:noNamespaceSchemaLocation="
http://www.companyname.com/EmployeeDetails"
>
6 <EmployeeID>102</EmployeeID>
7 </EmployeeDetail>
8 ')
9 );
VALUES(102,XMLTYPE('<?xml version="1.0"?>
*
ERROR at line 2:
ORA-31154: invalid XML document
ORA-19202: Error occurred in XML processing
LSX-00213: only 0 occurrences of particle "EmployeeName", minimum is 1
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at "SCOTT.TRGBEFOREUPDATEEMPLOYEEDETAILS", line 5
ORA-04088: error during execution of trigger
'SCOTT.TRGBEFOREUPDATEEMPLOYEEDETAILS'
SQL>
"George" <GeorgeFernley_at_hotmail.com> wrote in message
news:846a42dd.0402170356.2cc7ea29_at_posting.google.com...
> Would it be possible for someone to execute the script below and
> confirm the observed behaviour. When I attempt to perform this
> operation I see
>
> <snip>
>
> SQL>
> SQL>
> SQL> INSERT INTO tblEmployeeDetails(ID,EmployeeDetail)
> 2 VALUES(101,XMLTYPE('<?xml version="1.0"?>
> 3 <EmployeeDetail
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> 4
> xsi:noNamespaceSchemaLocation="http://www.companyname.com/EmployeeDetail
> s"
> 5 >
> 6 <EmployeeID>100</EmployeeID>
> 7 <EmployeeName>test string</EmployeeName>
> 8 </EmployeeDetail>
> 9 ')
> 10 );
> INSERT INTO tblEmployeeDetails(ID,EmployeeDetail)
> *
> ERROR at line 1:
> ORA-03113: end-of-file on communication channel
>
>
> SQL>
> SQL>
> SQL>
>
>
> The full session transcript of what is seen, including Oracle version
> number etc, follows the source which I've included immediately below
> (to enable copy/pasting for easy testing). I've executed the same
> script on a couple of Oracle XMLDB 9.2 databases under Win2K and see
> the same thing. There's a problem but I don't know what it is or how
> to get around it.
> Thank you
> George
>
>
>
>
> CONN SYSTEM/PASSWORD_at_DBXMLTEST
>
> SELECT * FROM V$VERSION;
>
> DROP USER "GEORGE" CASCADE;
> CREATE USER "GEORGE" PROFILE "DEFAULT"
> IDENTIFIED BY "GEORGE" DEFAULT TABLESPACE "USERS" ACCOUNT UNLOCK;
> GRANT "CONNECT" TO "GEORGE";
> GRANT CREATE ANY DIRECTORY TO "GEORGE";
> GRANT CREATE PROCEDURE TO "GEORGE";
> GRANT QUERY REWRITE TO "GEORGE";
> GRANT CREATE TYPE TO "GEORGE";
> GRANT CREATE TABLE TO "GEORGE";
> GRANT CREATE TRIGGER TO "GEORGE";
> ALTER USER "GEORGE" QUOTA 10M ON "USERS";
>
>
> CONN GEORGE/GEORGE_at_DBXMLTEST
>
> DECLARE
> xsd VARCHAR2(4000) := '<schema
>
> targetNamespace="http://www.companyname.com/EmployeeDetails"
> xmlns="http://www.w3.org/2001/XMLSchema"
> >
> <element name="EmployeeDetail">
> <complexType>
> <sequence>
> <element name="EmployeeID"
> type="integer"/>
> <element name="EmployeeName"
> type="string"/>
> </sequence>
> </complexType>
> </element>
> </schema>';
> BEGIN
>
DBMS_XMLSCHEMA.REGISTERSCHEMA('
http://www.companyname.com/EmployeeDetails',x
sd);
> END;
> /
>
>
> CREATE TABLE tblEmployeeDetails(ID NUMBER PRIMARY KEY,
> EmployeeDetail XMLTYPE
> ) XMLTYPE COLUMN EmployeeDetail
> ELEMENT
> "http://www.companyname.com/EmployeeDetails#EmployeeDetail";
>
>
> CREATE OR REPLACE TRIGGER trgBeforeUpdateEmployeeDetails
> BEFORE INSERT ON tblEmployeeDetails
> FOR EACH ROW
> BEGIN
> :NEW.EmployeeDetail.SCHEMAVALIDATE();
> END;
> /
>
>
> INSERT INTO tblEmployeeDetails(ID,EmployeeDetail)
> VALUES(101,XMLTYPE('<?xml version="1.0"?>
> <EmployeeDetail
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>
> xsi:noNamespaceSchemaLocation="http://www.companyname.com/EmployeeDetails"
> >
> <EmployeeID>100</EmployeeID>
> <EmployeeName>test string</EmployeeName>
> </EmployeeDetail>
> ')
> );
>
>
>
>
>
> ==========================================
> Transcript/what I see below.
> ==========================================
>
> SQL>
> SQL> CONN SYSTEM/PASSWORD_at_DBXMLTEST
> Connected.
> SQL>
> SQL> SELECT * FROM V$VERSION;
>
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> PL/SQL Release 9.2.0.1.0 - Production
> CORE 9.2.0.1.0 Production
> TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
> NLSRTL Version 9.2.0.1.0 - Production
>
> SQL>
> SQL> DROP USER "GEORGE" CASCADE;
>
> User dropped.
>
> SQL> CREATE USER "GEORGE" PROFILE "DEFAULT"
> 2 IDENTIFIED BY "GEORGE" DEFAULT TABLESPACE "USERS" ACCOUNT
> UNLOCK;
>
> User created.
>
> SQL> GRANT "CONNECT" TO "GEORGE";
>
> Grant succeeded.
>
> SQL> GRANT CREATE ANY DIRECTORY TO "GEORGE";
>
> Grant succeeded.
>
> SQL> GRANT CREATE PROCEDURE TO "GEORGE";
>
> Grant succeeded.
>
> SQL> GRANT QUERY REWRITE TO "GEORGE";
>
> Grant succeeded.
>
> SQL> GRANT CREATE TYPE TO "GEORGE";
>
> Grant succeeded.
>
> SQL> GRANT CREATE TABLE TO "GEORGE";
>
> Grant succeeded.
>
> SQL> GRANT CREATE TRIGGER TO "GEORGE";
>
> Grant succeeded.
>
> SQL> ALTER USER "GEORGE" QUOTA 10M ON "USERS";
>
> User altered.
>
> SQL>
> SQL>
> SQL> CONN GEORGE/GEORGE_at_DBXMLTEST
> Connected.
> SQL>
> SQL> DECLARE
> 2 xsd VARCHAR2(4000) := '<schema
> 3
> targetNamespace="http://www.companyname.com/EmployeeDetails"
> 4 xmlns="http://www.w3.org/2001/XMLSchema"
> 5 >
> 6 <element name="EmployeeDetail">
> 7 <complexType>
> 8 <sequence>
> 9 <element name="EmployeeID"
> type="integer"/>
> 10 <element name="EmployeeName"
> type="string"/>
> 11 </sequence>
> 12 </complexType>
> 13 </element>
> 14 </schema>';
> 15 BEGIN
> 16
DBMS_XMLSCHEMA.REGISTERSCHEMA('
http://www.companyname.com/EmployeeDetails',x
sd);
> 17 END;
> 18 /
>
> PL/SQL procedure successfully completed.
>
> SQL>
> SQL>
> SQL> CREATE TABLE tblEmployeeDetails(ID NUMBER PRIMARY KEY,
> 2 EmployeeDetail XMLTYPE NOT
> NULL
> 3 ) XMLTYPE COLUMN
> EmployeeDetail
> 4 ELEMENT
> "http://www.companyname.com/EmployeeDetails#EmployeeDetail";
>
> Table created.
>
> SQL>
> SQL>
> SQL> CREATE OR REPLACE TRIGGER trgBeforeUpdateEmployeeDetails
> 2 BEFORE INSERT ON tblEmployeeDetails
> 3 FOR EACH ROW
> 4 BEGIN
> 5 :NEW.EmployeeDetail.SCHEMAVALIDATE();
> 6 END;
> 7 /
>
> Trigger created.
>
> SQL>
> SQL>
> SQL> INSERT INTO tblEmployeeDetails(ID,EmployeeDetail)
> 2 VALUES(101,XMLTYPE('<?xml version="1.0"?>
> 3 <EmployeeDetail
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
> 4
> xsi:noNamespaceSchemaLocation="http://www.companyname.com/EmployeeDetails"
> 5 >
> 6 <EmployeeID>100</EmployeeID>
> 7 <EmployeeName>test string</EmployeeName>
> 8 </EmployeeDetail>
> 9 ')
> 10 );
> INSERT INTO tblEmployeeDetails(ID,EmployeeDetail)
> *
> ERROR at line 1:
> ORA-03113: end-of-file on communication channel
>
>
> SQL>
> SQL>
Received on Tue Feb 17 2004 - 10:26:03 CST
