This discussion is archived
4 Replies Latest reply: Oct 1, 2012 10:43 AM by 959086 RSS

Problem creating structured XMLIndex with parameters

959086 Newbie
Currently Being Moderated
I'm trying to get the following SQL to run in Toad...
CREATE INDEX AUDITABLE_EVENT_XML_IDX_MIN ON AUDITABLE_EVENT_XML (XML_EVENT_CONTENT) 
    INDEXTYPE IS XDB.XMLIndex
    PARAMETERS ('XMLTable AUDITABLE_EVENT_TAB_MIN 
            XMLNAMESPACES("http://gov/va/med/datasharing/audit/endpoint/audit" AS "xae"),
            "/xae:auditable-event"
            COLUMNS
            EVENT_TYPE       VARCHAR2(255) PATH "xae:event-type",
            SENDING_SITE     VARCHAR2(255) PATH "xae:sending-site",
            RECEIVING_SITE   VARCHAR2(255) PATH "xae:receiving-site",
            EVENT_ID         VARCHAR2(255) PATH "xae:event-id",
            PATIENT_ID       VARCHAR2(255) PATH "xae:patient-id",
            OUTCOME          VARCHAR2(255) PATH "xae:outcome"'
    );
I'm getting this error:
ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
ORA-30969: invalid syntax for PARAMETERS
The syntax looks good to me.
There is no registered schema; but it seems that this should still work since the XPath is predictable...
Is there someplace where the PARAMETERS are documented?
This must be something small that I'm overlooking... Just not sure...

Here is the code to create the table:
CREATE TABLE AUDITABLE_EVENT_XML (
    AUDITED_EVENT_XML_PK_ID NUMBER(10) PRIMARY KEY,
    /* The time the audit record was created. */
    CREATED_TIME TIMESTAMP(6) WITH LOCAL TIME ZONE NOT NULL,
    /* Well-formed XML message */
    XML_EVENT_CONTENT XMLType
    )
    XMLTYPE COLUMN "XML_EVENT_CONTENT" STORE AS BINARY XML;
Any assistance would be greatly appreciated...
  • 1. Re: Problem creating structured XMLIndex with parameters
    odie_63 Guru
    Currently Being Moderated
    Is there someplace where the PARAMETERS are documented?
    This must be something small that I'm overlooking... Just not sure...
    Double-quotes are the problem.
    http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_indexing.htm#BCGDDBDI

    The syntax is similar to that of XMLTable function, XQuery expressions and namespace targets must be enclosed within single quotes.
    Therefore, in the PARAMETERS clause, they must be escaped by either doubling them, or using the quote operator :
    CREATE INDEX AUDITABLE_EVENT_XML_IDX_MIN ON AUDITABLE_EVENT_XML (XML_EVENT_CONTENT) 
        INDEXTYPE IS XDB.XMLIndex
        PARAMETERS ('XMLTable AUDITABLE_EVENT_TAB_MIN 
                XMLNAMESPACES(''http://gov/va/med/datasharing/audit/endpoint/audit'' AS "xae"),
                ''/xae:auditable-event''
                COLUMNS
                EVENT_TYPE       VARCHAR2(255) PATH ''xae:event-type'',
                SENDING_SITE     VARCHAR2(255) PATH ''xae:sending-site'',
                RECEIVING_SITE   VARCHAR2(255) PATH ''xae:receiving-site'',
                EVENT_ID         VARCHAR2(255) PATH ''xae:event-id'',
                PATIENT_ID       VARCHAR2(255) PATH ''xae:patient-id'',
                OUTCOME          VARCHAR2(255) PATH ''xae:outcome'''
        );
    CREATE INDEX AUDITABLE_EVENT_XML_IDX_MIN ON AUDITABLE_EVENT_XML (XML_EVENT_CONTENT) 
        INDEXTYPE IS XDB.XMLIndex
        PARAMETERS ( q'#
            XMLTable AUDITABLE_EVENT_TAB_MIN 
                XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                '/xae:auditable-event'
                COLUMNS
                EVENT_TYPE       VARCHAR2(255) PATH 'xae:event-type',
                SENDING_SITE     VARCHAR2(255) PATH 'xae:sending-site',
                RECEIVING_SITE   VARCHAR2(255) PATH 'xae:receiving-site',
                EVENT_ID         VARCHAR2(255) PATH 'xae:event-id',
                PATIENT_ID       VARCHAR2(255) PATH 'xae:patient-id',
                OUTCOME          VARCHAR2(255) PATH 'xae:outcome'
           #');
  • 2. Re: Problem creating structured XMLIndex with parameters
    959086 Newbie
    Currently Being Moderated
    I tried both of those suggestions in Toad and in SQL*Plus.
    This error occurred:

    ERROR at line 1:
    ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
    ORA-30969: invalid syntax for PARAMETERS
  • 3. Re: Problem creating structured XMLIndex with parameters
    959086 Newbie
    Currently Being Moderated
    Here is the version info:
    SQL*Plus: Release 11.1.0.6.0 - Production on Mon Sep 10 18:41:55 2012
     
    Copyright (c) 1982, 2007, Oracle.  All rights reserved.
     
     
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
  • 4. Re: Problem creating structured XMLIndex with parameters
    odie_63 Guru
    Currently Being Moderated
    Structured XMLIndexes are not available prior to 11.2.

Legend

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