Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

XQuery syntax OK in 11R2 but not working in 10R2

JurijCJan 31 2013 — edited Jan 31 2013
I'm new to XQuery and I'm struggling a bit with it, I've written an insert statement in our 11R2 environment and when it was deployed to a customer's 10R2 environment it throws an error:
select x.stmt
from source_table vdx1
,    xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')
              ,'for $i in /Document/BkToCstmrStmt/Stmt where $i/Bal/Amt/@Ccy = $ccy return $i'
              passing vdx1.content
              , cast(l_ccy_code as varchar2(3)) as "ccy"
              columns Stmt xmltype path '/*'
             ) x
where vdx1.id = l_vdx_id

ORA-00932: inconsistent datatypes: expected - got CHAR
The problem consists in the line with "cast", I basically need only the data where the value of /Bal/Amt/@Ccy is equal to a certain value which is stored in the variable l_ccy_code. This value varies, so I cannot just hard-code it.

Is there a way to write this select statement that would be correct for both 10R2 and 11R2 environments? Is there a better way to write the XQuery for my case, this is what I came up with, but due to my lack of experience with XQuery it might not be the optimal solution?
This post has been answered by odie_63 on Jan 31 2013
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 28 2013
Added on Jan 31 2013
2 comments
239 views