Friday, October 22, 2010

XMLTable with a Parameter

I found this question on the OTN forums, Problem with XMLTABLE with parameters, interesting becausea) I knew there was a way to accomplish thatb) I couldn't quickly turn it up via Google till I got the right keywords.

After seeing the solution that I ended up borrowing, I first checked the Oracle documentation on XMLTable for 10.2 to verify this was valid. As you can see by the documentation, the XML_passing_clause does allow for multiple expressions.

Curious as to see how this worked for different data types and on 10g and 11g, I created the following setup.

With my setup, Oracle knows nothing about the XML in terms of data types so 2 and 1980-01-01 are simply strings to it. So, even though I was CASTing a string to a DATE, Oracle is converting the value back to a string during the XPath evaluation. (Note: This is not confirmed but highly suspected).

So why is using CAST good? It comes in useful when you have a schema registered within Oracle and the XMLType is based off of the schema. This provides Oracle with data type information and is especially true for Object Relational Storage where it is used to create data types for columns. Oracle knows the data type of the node so you want to pass in the same data type using an explicit conversion. This is the basic reasoning why you don't want to compare a number to a string. Oracle will implicitly convert one to the other and can cause issues it if encounters a string with "a1" in it that fails to convert to a number.. That is the purpose of CAST, so you can tell Oracle that a given parameter is a number or a date to avoid the implicit conversion.

Gotchas

Running the above XMLTable with CAST SQL Statements will result in an ORA000932: inconsistent datatypes: expected - got

About Me

I do what I do because I like it. I've had exposure to a lot of different products/technology over the time. I started with COBOL and flat files on a mainframe. I've used VSAM and DB2 there as well. Linux and Solaris have crossed my path several times. XML, XSLT and schemas have been friends for a while. SQL has seen me through MS Access, DB2 and Oracle. I've worked with 2 versions of DB2 and 3 of Oracle. There has been many other things as well, too small to mention.