2 Replies Latest reply: Oct 31, 2012 12:36 PM by TrojanSpirit RSS

    Xquery Error due to Upper/Lower Case

    TrojanSpirit
      Hello,

      When I am writing my Xquery like following, it works !

      Select RW.*
      from ULQUEUE UL, XMLTABLE('//Row' PASSING UL.TEXT
      COLUMNS
      *"EVENT" CHAR(10) PATH 'Event',*
      *"REASONCODE" CHAR(10) PATH 'Reason_Code',*
      *"DESCRIPTION" CHAR(10) PATH 'Description'*
      *) AS RW*
      where
      *UL.ID = '[Param.1]' and*
      *UL.QUEUENAME = '[Param.2]' and*
      *RW.EVENT = '[Param.3]' and*
      *RW.REASONCODE = '[Param.4]'*

      But I need to have Headers same as XML Node. SO when I write following query, it does not work :

      Select RW.*
      from ULQUEUE UL, XMLTABLE('//Row' PASSING UL.TEXT
      COLUMNS
      *"Event" CHAR(10) PATH 'Event',*
      *"Reason_Code" CHAR(10) PATH 'Reason_Code',*
      *"Description" CHAR(10) PATH 'Description'*
      *) AS RW*
      where
      *UL.ID = '[Param.1]' and*
      *UL.QUEUENAME = '[Param.2]' and*
      *RW.Event = '[Param.3]' and*
      *RW.Reason_Code = '[Param.4]'*

      It gives me this error: java.sql.SQLSyntaxErrorException: ORA-00904: "RW"."REASON_CODE": invalid identifier

      If I remove RW.Reason_Code = '[Param.4]' from where clause, it gives me this error: java.sql.SQLSyntaxErrorException: ORA-00904: "RW"."EVENT": invalid identifier

      So just want to know, why in UPPERCASE the query works but in camel or Lowercase it does not.

      What should I do to make columnheaders same as XML Nodes?

      Edited by: 967327 on Oct 30, 2012 11:33 PM
        • 1. Re: Xquery Error due to Upper/Lower Case
          odie_63
          Hi,

          The error has nothing to do with XQuery or XML.

          In Oracle, identifiers are case-insensitive unless they are "quoted".
          If you decide to use quoted identifiers, then you have to use them like this all the way down :
          SELECT RW.*
          FROM ulqueue ul
             , XMLTABLE('//Row' PASSING UL.TEXT
                 COLUMNS
                   "Event"        CHAR(10) PATH 'Event'
                 , "Reason_Code"  CHAR(10) PATH 'Reason_Code'
                 , "Description"  CHAR(10) PATH 'Description'
               ) as rw
          where ul.id = '[Param.1]' 
          and ul.queuename = '[Param.2]' 
          and rw."Event" = '[Param.3]' 
          and rw."Reason_Code" = '[Param.4]'
          ;
          What should I do to make columnheaders same as XML Nodes?
          You cannot, the SQL projection has to be known at parse time, in other words column names must be hardcoded in the query.
          • 2. Re: Xquery Error due to Upper/Lower Case
            TrojanSpirit
            Thanks a lot.

            Yes, writing a query with

            *and rw."Event" = '[Param.3]'*
            *and rw."Reason_Code" = '[Param.4]'*

            does solve my problem.