Database Software

Hi,
We get error trying to create XMLIndex on Binary XML Table with TABLESPACE specification included. Did check Parenthesis and they are all matched up in CREATE INDEX Statement:

 
SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 28 11:50:46 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> SET ECHO ON
SQL> SET FEED ON
SQL> SET SQLTERMINATOR OFF

SQL> CREATE TABLE CSES_AGREEMENT_XML (
  2     AGREEMENT_XML XMLType NOT NULL,
  3     CREATE_BY VARCHAR2(15),
  4     CREATE_DT_GMT TIMESTAMP(6),
  5     CREATE_CLIENT_ID VARCHAR2(65),
  6     UPDATE_BY VARCHAR2(15),
  7     UPDATE_DT_GMT TIMESTAMP(6),
  8     UPDATE_CLIENT_ID VARCHAR2(65),
  9     PIC_VERSION_NUM VARCHAR2(20))
 10  TABLESPACE CSES_LG_DATA XMLTYPE "AGREEMENT_XML"
 11  STORE AS BINARY XML
 12  VIRTUAL COLUMNS
 13  (OBJECT_ID AS (XMLCast(XMLQuery('declare default element namespace ''http:/
/com.oocl.schema.tnm.agreementbuilder'';
 14  declare namespace xsi=''http://www.w3.org/2001/XMLSchema-instance''; (: :)
 15  declare namespace xsd=''http://www.w3.org/2001/XMLSchema''; (: :) /Agreemen
t/ObjectID'
 16  PASSING AGREEMENT_XML RETURNING CONTENT)
 17  AS NUMBER(20))),
 18  AGREEMENT_ID AS (XMLCast(XMLQuery('declare default element namespace ''http
://com.oocl.schema.tnm.agreementbuilder'';
 19  declare namespace xsi=''http://www.w3.org/2001/XMLSchema-instance''; (: :)
 20  declare namespace xsd=''http://www.w3.org/2001/XMLSchema''; (: :) /Agreemen
t/Identifier'
 21  PASSING AGREEMENT_XML RETURNING CONTENT)
 22  AS NUMBER(10))),
 23  CREATION_DATE AS (XMLCast(XMLQuery('declare default element namespace ''htt
p://com.oocl.schema.tnm.agreementbuilder'';
 24  declare namespace xsi=''http://www.w3.org/2001/XMLSchema-instance''; (: :)
 25  declare namespace xsd=''http://www.w3.org/2001/XMLSchema''; (: :) /Agreemen
t/CreationDate'
 26  PASSING AGREEMENT_XML RETURNING CONTENT)
 27  AS TIMESTAMP)),
 28  LAST_UPDATED_BY AS (XMLCast(XMLQuery('declare default element namespace ''h
ttp://com.oocl.schema.tnm.agreementbuilder'';
 29  declare namespace xsi=''http://www.w3.org/2001/XMLSchema-instance''; (: :)
 30  declare namespace xsd=''http://www.w3.org/2001/XMLSchema''; (: :) /Agreemen
t/LastUpdatedBy'
 31  PASSING AGREEMENT_XML RETURNING CONTENT)
 32  AS VARCHAR2(10))))
 33  /

Table created.

SQL> ALTER TABLE CSES_AGREEMENT_XML
  2    ADD CONSTRAINT CSES_AGREEMENT_XML_PK PRIMARY KEY (OBJECT_ID)
  3    USING INDEX TABLESPACE CSES_LG_INDX
  4  /

Table altered.

SQL> ALTER TABLE CSES_AGREEMENT_XML
  2    ADD CONSTRAINT CSES_AGREEMENT_XML_UDX1 UNIQUE (AGREEMENT_ID)
  3    USING INDEX TABLESPACE CSES_LG_INDX
  4  /

Table altered.

SQL> CREATE INDEX CSES_AGREEMENT_XML_IDX1
  2    ON  CSES_AGREEMENT_XML( LAST_UPDATED_BY )
  3        TABLESPACE CSES_LG_INDX
  4  /

Index created.

SQL> CREATE INDEX CSES_AGREEMENT_XML_IDX2
  2    ON  CSES_AGREEMENT_XML( CREATION_DATE )
  3        TABLESPACE CSES_LG_INDX
  4  /

Index created.

SQL> CREATE INDEX CSES_AGREEMENT_XML_XDX1  ON CSES_AGREEMENT_XML(AGREEMENT_XML)

  2  INDEXTYPE IS XDB.XMLINDEX
  3  PARAMETERS (
  4  'GROUP CSES_AGREEMENT_XML_XDX_GRP0
  5   XMLTABLE CSES_AGREEMENT_XML_XTAB0 (TABLESPACE CSES_LG_INDX)
  6   XMLNAMESPACES(default ''http://com.oocl.schema.tnm.agreementbuilder''),
  7   ''/Agreement''
  8   COLUMNS
  9     SHP_PTY_CUST_HOLDER XMLType PATH ''ShippingPartyGroups/ShippingParties/C
ustomerHolder'' VIRTUAL
 10     XMLTABLE CSES_AGREEMENT_XML_XTAB1 (TABLESPACE CSES_LG_INDX)
 11      XMLNAMESPACES(default ''http://com.oocl.schema.tnm.agreementbuilder''),

 12        ''/CustomerHolder'' PASSING SHP_PTY_CUST_HOLDER
 13      COLUMNS
 14          SAP_ID VARCHAR2(10) PATH ''SAP_ID''')
 15  /
CREATE INDEX CSES_AGREEMENT_XML_XDX1  ON CSES_AGREEMENT_XML(AGREEMENT_XML)
*
ERROR at line 1:
ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
ORA-00907: missing right parenthesis

SQL>

Find XMLIndex creation works fine without Tablespace Specification (TABLESPACE CSES_LG_INDX) the Index Creation works fine. However, we do need to specify proper TABLESPACE spec for the setup.

SQL>
SQL>
SQL> -- Works fine without TABLESPACE Specification for XMLTABLE clause
SQL> CREATE INDEX CSES_AGREEMENT_XML_XDX1  ON CSES_AGREEMENT_XML(AGREEMENT_XML)

  2  INDEXTYPE IS XDB.XMLINDEX
  3  PARAMETERS (
  4  'GROUP CSES_AGREEMENT_XML_XDX_GRP0
  5   XMLTABLE CSES_AGREEMENT_XML_XTAB0
  6   XMLNAMESPACES(default ''http://com.oocl.schema.tnm.agreementbuilder''),
  7   ''/Agreement''
  8   COLUMNS
  9     SHP_PTY_CUST_HOLDER XMLType PATH ''ShippingPartyGroups/ShippingParties/C
ustomerHolder'' VIRTUAL
 10     XMLTABLE CSES_AGREEMENT_XML_XTAB1
 11      XMLNAMESPACES(default ''http://com.oocl.schema.tnm.agreementbuilder''),

 12        ''/CustomerHolder'' PASSING SHP_PTY_CUST_HOLDER
 13      COLUMNS
 14          SAP_ID VARCHAR2(10) PATH ''SAP_ID''')
 15  /

Index created.

SQL>

Any help or pointers would be greatly appreciated.

Thanks
Auroprem