DATE/TIME datatypes and Oracle Database

[Former Member]

Every Oracle developer knows that the best way to avoid version and format compatibility issues with DATE and TIME is to use the TO_DATE() function:         UPDATE TABLE_EXAMPLE           SET DATE_EXAMPLE = TO_DATE(‘2005-07-11’,’YYYY-MM-DD’)

Using TO_DATE() you explicitly declare the format of the date field, independently from the settings or the locale of the database

With XI you can easily access databases using the XML SQL message protocol:

Each standard SQL statement is rendered with a predefined datatype structure like the one in the figure below:

With the XML SQL you:

• Define the type of operation (SELECT, UPDATE, INSERT, DELETE) in the (action) attribute
• Set the table name in the (table) field
• Define the fields (access) and the key (key) of your SQL Statement, and the datatype of each field

You can find more info on XML SQL in the XI documentation: "Document formats for the receiver JDBC adapter"

Unfortunately, this doesn’t work for DATE/TIME types if you try to access an Oracle database: the DBMS is not able to recognize the date/time format and you get a type mismatch error.

How can we avoid that using the TO_DATE() function with the XML SQL format? With the attribute hasQuot we can force the JDBC processor not to add quotes (‘’) around the content of a string field: Than we can put into that field the whole TO_DATE() statement using standard mapping functions. Let see that in detail. First, we should declare as string each DATE/TIME field and add the hasQuot attribute, like in the following figure: Then in our mapping we set the hasQuot attribute to “No”: And we build the TO_DATE()statement with the appropriate concat() functions and constant As you can see, the content of the field is exactly the same you would use in your SQL Statement and the hasQuot=’No’ attribute let this statement go unchanged to the DBMS. Obviously in your mapping you can use a variable field for your date instead of a constant but be sure to match the format specified in the TO_DATE() function.