Problem with Oracle XML database - verification of problem
This is a discussion on Problem with Oracle XML database - verification of problem within the Oracle Database forums, part of the Database Server Software category; --> Would it be possible for someone to execute the script below and confirm the observed behaviour. When I attempt ...
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
02-23-2008, 07:00 AM
| ||||
| ||||
 Problem with Oracle XML database - verification of problem 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@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@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',xsd); 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@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@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',xsd); 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>      |
|
02-23-2008, 07:00 AM
| ||||
| ||||
| Re: Problem with Oracle XML database - verification of problem 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@hotmail.com> wrote in message news:846a42dd.0402170356.2cc7ea29@posting.google.c om... > 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@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@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@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@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> |
Linear Mode
