5 Replies Latest reply: Nov 27, 2012 12:43 PM by odie_63 RSS

    Using the variable ? operator in an XPath Expression

    959086
      I'm trying to using the variable operator (?) in an Oracle prepared statement used in a query where clause. Here is the query:
      select xt.APPLICATION_NAME, xt.VERSION, xt.EVENT_TYPE, xt.SENDING_SITE, xt.RECEIVING_SITE, xt.EVENT_ID 
            from AUDITED_EVENT_XML_MIN e,
               XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                      '/xae:auditable-event'
                      PASSING e.xml_event_content
                      COLUMNS
                      APPLICATION_NAME VARCHAR2(255) PATH 'xae:application-name',
                      VERSION          VARCHAR2(255) PATH 'xae:version',
                      EVENT_TYPE       VARCHAR2(255) PATH 'xae:event-type',
                      SENDING_SITE     VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.4/HD.2',
                      RECEIVING_SITE   VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.6/HD.2',
                      EVENT_ID         VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.10') xt
                  where existsNode(E.XML_EVENT_CONTENT, '/xae:auditable-event[xae:event-type=?]','xmlns:xae="http://gov/va/med/datasharing/audit/endpoint/audit"') = 1
      This code works when the ? is replaced with "aValue". I need to use the ? so that my Java client can pass a variable value into the query.
      Is this a supportable feature or am I doing something wrong?
      I didn't see any examples in the Oracle XML DB Developers Guide where there was a ? in an XPath expression.
      I also tried "?" with the same issue...

      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
      Any assistance would be greatly appreciated.
      Thanks in advance...
        • 1. Re: Using the variable ? operator in an XPath Expression
          AlexAnd
          you can try to use bind variable
          (Oracle® XML DB Developer's Guide 10g Release 2 (10.2)
          -- 6 XPath Rewrite
          -- Which XPath Expressions Are Rewritten?
          -- Table 6-1 Sample List of XPath Expressions for Translation to Underlying SQL constructs
          -- String bind variables inside predicates
          -- http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb_rewrite.htm#sthref878)
          select xt.APPLICATION_NAME, xt.VERSION, xt.EVENT_TYPE, xt.SENDING_SITE, xt.RECEIVING_SITE, xt.EVENT_ID 
                from AUDITED_EVENT_XML_MIN e,
                   XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                          '/xae:auditable-event'
                          PASSING e.xml_event_content
                          COLUMNS
                          APPLICATION_NAME VARCHAR2(255) PATH 'xae:application-name',
                          VERSION          VARCHAR2(255) PATH 'xae:version',
                          EVENT_TYPE       VARCHAR2(255) PATH 'xae:event-type',
                          SENDING_SITE     VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.4/HD.2',
                          RECEIVING_SITE   VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.6/HD.2',
                          EVENT_ID         VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.10') xt
                      where existsNode(E.XML_EVENT_CONTENT, '/xae:auditable-event[xae:event-type="'|| ? || '"]','xmlns:xae="http://gov/va/med/datasharing/audit/endpoint/audit"') = 1
          • 2. Re: Using the variable ? operator in an XPath Expression
            959086
            Thank you for your assistance. This worked very well...

            Now I would like to take it to the next level. I would like to specify this SQL statically, then add the XPath expression in the where clause dynamically in my Java client.

            Here is the static SQL:
            select xt.APPLICATION_NAME, xt.VERSION, xt.EVENT_TYPE, xt.SENDING_SITE, xt.RECEIVING_SITE, xt.EVENT_ID 
                  from AUDITED_EVENT_XML_MIN e,
                     XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                            '/xae:auditable-event'
                            PASSING e.xml_event_content
                            COLUMNS
                            APPLICATION_NAME VARCHAR2(255) PATH 'xae:application-name',
                            VERSION          VARCHAR2(255) PATH 'xae:version',
                            EVENT_TYPE       VARCHAR2(255) PATH 'xae:event-type',
                            SENDING_SITE     VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.4/HD.2',
                            RECEIVING_SITE   VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.6/HD.2',
                            EVENT_ID         VARCHAR2(255) PATH 'xae:xml-event-content/*/MSH/MSH.10') xt
                        where existsNode(E.XML_EVENT_CONTENT,' || ? || ','xmlns:xae="http://gov/va/med/datasharing/audit/endpoint/audit"') = 1
            I want to compute the XPath expression is Java and pass it as a variable into the prepared statement.
            When I try this, I get a SQLException: Invalid column index. I usually get this error when the SQL is not coded correctly to accept a parameter.

            Is there a way to write the SQL so that this will work?
            How do you recommend implementing a DAO that can support a combination of different values in the where clause that must evaluate if a value is in a set of known values?

            Thanks in advance...
            • 3. Re: Using the variable ? operator in an XPath Expression
              AlexAnd
              did you try
              where existsNode(E.XML_EVENT_CONTENT, ? ,'xmlns:xae="http://gov/va/med/datasharing/audit/endpoint/audit"') = 1
              ?
              • 4. Re: Using the variable ? operator in an XPath Expression
                959086
                I incorporated this suggestion and it worked very well. Thanks!

                Now I have converted my solution to use a structured index. As such, I want to write the where clauses in terms of column names rather than XPath expressions.
                I hoped it was as simple as changing the code that generates the XPath expression to generate a SQL expression. After making that change, I'm now getting this error:
                Caused by: java.sql.SQLSyntaxErrorException: ORA-00920: invalid relational operator
                
                     at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
                     at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
                     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
                     at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:686)
                     at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:617)
                     at oracle.jdbc.driver.T2CPreparedStatement.executeForDescribe(T2CPreparedStatement.java:559)
                     at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1077)
                     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186)
                     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3381)
                     at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3425)
                     at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1490)
                     at gov.va.med.datasharing.audit.dao.jdbc.runner.MapQueryRunner.run(MapQueryRunner.java:47)
                     at gov.va.med.datasharing.audit.web.dao.jdbc.AuditableEventsQueryDAOImpl.getAuditableEventsByDate(AuditableEventsQueryDAOImpl.java:150)
                It seemed to me that if it worked for the XPath expression that it should also work for the SQL expression.
                The static SQL is:
                SELECT PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID FROM
                  (SELECT rownum as rn, PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID FROM
                     (SELECT aet.AUDITABLE_EVENT_XML_PK_ID AS PK_ID, xt.EVENT_TYPE, xt.OUTCOME, xt.SENDING_SITE, xt.RECEIVING_SITE, aet.CREATED_TIME as CREATED_TIME, xt.EVENT_ID, xt.PATIENT_ID
                        FROM AUDITABLE_EVENT_XML aet,
                          XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                            '/xae:auditable-event'
                            PASSING aet.xml_event_content
                            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') xt
                        WHERE aet.CREATED_TIME BETWEEN ? AND ?
                             AND ?
                      ) ORDER BY CREATED_TIME desc
                    ) WHERE rn > ? and rn <= ?
                So I'm thinking that Oracle must perform strong checking in SQL, not allowing the '=' operator to be substituted. If I run this query in Toad, replacing the ? with the SQL, it runs fine...

                Can someone tell me whether or not this is a legal substitution?

                The SQL that runs in Toad is:
                SELECT PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID FROM
                  (SELECT rownum as rn, PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID FROM
                     (SELECT aet.AUDITABLE_EVENT_XML_PK_ID AS PK_ID, xt.EVENT_TYPE, xt.OUTCOME, xt.SENDING_SITE, xt.RECEIVING_SITE, aet.CREATED_TIME as CREATED_TIME, xt.EVENT_ID, xt.PATIENT_ID
                        FROM AUDITABLE_EVENT_XML aet,
                          XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                            '/xae:auditable-event'
                            PASSING aet.xml_event_content
                            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') xt
                        WHERE aet.CREATED_TIME BETWEEN TO_DATE ('9/19/2011 12:00:01 AM','MM/DD/YYYY HH:MI:SS PM')
                                                     AND TO_DATE ('12/20/2012 12:00:00 AM','MM/DD/YYYY HH:MI:SS PM')
                            AND (OUTCOME=0 or OUTCOME=1)
                      ) ORDER BY CREATED_TIME desc
                    ) WHERE rn > 0 and rn <= 5;
                Any assistance will be appreciated,
                Thanks in advance.
                • 5. Re: Using the variable ? operator in an XPath Expression
                  odie_63
                  Can someone tell me whether or not this is a legal substitution?
                  No, that's not legal.

                  ? defines a placeholder for a bind variable, that's all it can do, binds a variable (value) at execution time, it doesn't handle identifiers and least of all movable pieces of code.

                  You'll have to use concatenation to add the code fragment :
                  " ... AND " + myFilter + " ... remaining for the query string"
                  BTW, I see you're trying to build a pagination query, but the ORDER BY is misplaced, it must be in the innermost subquery, not at the same level as ROWNUM :
                  SELECT PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID 
                  FROM (
                    SELECT rownum as rn, PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID 
                    FROM (
                      SELECT aet.AUDITABLE_EVENT_XML_PK_ID AS PK_ID, xt.EVENT_TYPE, xt.OUTCOME, xt.SENDING_SITE, xt.RECEIVING_SITE, aet.CREATED_TIME as CREATED_TIME, xt.EVENT_ID, xt.PATIENT_ID
                      FROM AUDITABLE_EVENT_XML aet,
                           XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                             '/xae:auditable-event'
                             PASSING aet.xml_event_content
                             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'
                           ) xt
                      WHERE aet.CREATED_TIME BETWEEN ? AND ?
                      AND (OUTCOME=0 or OUTCOME=1)
                      ORDER BY CREATED_TIME desc
                    )
                    WHERE rownum <= ? 
                  ) 
                  WHERE rn > ?
                  or using ROW_NUMBER() :
                  SELECT PK_ID, EVENT_TYPE, OUTCOME, SENDING_SITE, RECEIVING_SITE, CREATED_TIME, EVENT_ID, PATIENT_ID 
                  FROM (
                    SELECT aet.AUDITABLE_EVENT_XML_PK_ID AS PK_ID, xt.EVENT_TYPE, xt.OUTCOME, xt.SENDING_SITE, xt.RECEIVING_SITE, aet.CREATED_TIME as CREATED_TIME, xt.EVENT_ID, xt.PATIENT_ID
                         , ROW_NUMBER() OVER(ORDER BY created_time DESC) rn
                    FROM AUDITABLE_EVENT_XML aet,
                         XMLTable(XMLNAMESPACES('http://gov/va/med/datasharing/audit/endpoint/audit' AS "xae"),
                           '/xae:auditable-event'
                           PASSING aet.xml_event_content
                           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'
                         ) xt
                    WHERE aet.CREATED_TIME BETWEEN ? AND ?
                    AND (OUTCOME=0 or OUTCOME=1)
                  )
                  WHERE rn <= ?
                  AND rn > ?