4 Replies Latest reply: Oct 1, 2012 12:47 PM by odie_63 RSS

    Problem creating structured XMLIndex with parameters

    959086
      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
          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
            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
              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
                Structured XMLIndexes are not available prior to 11.2.