This discussion is archived
2 Replies Latest reply: Oct 31, 2012 10:36 AM by TrojanSpirit RSS

Xquery Error due to Upper/Lower Case

TrojanSpirit Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot.

    Yes, writing a query with

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

    does solve my problem.

Legend

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