Problem with Oracle XML database - verification of problem

From: George <GeorgeFernley_at_hotmail.com>
Date: 17 Feb 2004 03:56:14 -0800
Message-ID: <846a42dd.0402170356.2cc7ea29@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',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_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',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> Received on Tue Feb 17 2004 - 05:56:14 CST