Retrieving Data From Returned Oracle Timestamp Column

Question

We have a ColdFusion 8 (Linux) application that uses an Oracle timestamp. We just converted to Oracle 11g from 10g and we're now using Oracle's thin client on the data sources. We're getting an error in the application where a timestamp column is selected.

It seems as though an object of class oracle.sql.TIMESTAMP is being returned. I verified this by dumping the contents of the column. Sure enough, it gives me a break down of the object's methods and their return types. But, I can't seem to be able to interface with this object directly:

<cfquery name="getstuff" ...>
    SELECT timestampfld ...
    FROM myTable
</cfquery>

getstuff.timestampfld contains an object. But, doing this:

<cfoutput query="getstuff">
    #timestampfld.stringValue()#
    #timestampfld.dateValue()#
</cfoutput>

produces the error that says those methods can't be found. How can I get at the data held in that object?

Update from comments:

When I take column value and apply the DateFormat( timestampfld, "dd.mm.yyyy" ) function to it. The CF error is

"The value class oracle.sql.timestamp cannot be converted to a date".

When I perform <cfoutput>, I get the class definition.

In the retrieved column, I seem to be getting an object instead of a string. When I cfdump the column, I get OBJECT OF oracle.sql.TIMESTAMP. The dump lays out the methods and fields available. When I cfoutput that same variable, a string is displayed. When I try to perform a DataFormat() on the variable, it complains that its not a date.

Answer 1

I just happened to stumble over this error during my development. I had it in the past and long forgotten since. I know of two ways to mitigate: The first is according to an answer of a question regarding the same error message in a different context. One would add the following to the jvm.config file

-Doracle.jdbc.J2EE13Compliant=true

The second is not to return an Oracle TIMESTAMP column but CAST it do DATE, first, like

<cfquery name="getstuff" ...>
  SELECT CAST( timestampfld as DATE ) timestampfld
  FROM myTable
</cfquery>

I'm not satisfied with either, to be honest. When the JVM argument is forgotten, the software crashes. The CAST, on the other hand, may influence how the SQL works.