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.

Tiffaneytiffani

Can you post the exact error (ie: don't just describe it). – Retired

And what if you just <cfoutput>#timestampfld#</cfoutput> ? – Retired

The CF error is "The value class oracle.sql.timestamp cannot be converted to a date". I was taking the column value and applying the DateFormat() function to it. When I perform <cfoutput> I get the class definition. – Tiffaneytiffani

Correction. When I output the timestamp column value I get a string containing the time/date contained. – Tiffaneytiffani

What is the format of the string? It is likely not one that the DateFormat() function can parse. Please add the output that is returned to your question so that we can help you further - edit – Nameless

" When I output the timestamp column value I get a string containing the time/date contained" So... what's the problem? – Balkh

@Duncan Precisely what I was thinking. – Retired

user10310142 (wanna give yerself a NAME??! ;-) when asked to post an error message, pls copy and paste the whole error - including the line of code it highlights and the top of the stack trace - and INCLUDE IT IN YOUR QUESTION (not as a comment). Same with values: don't describe the value (eg "When I output the timestamp column value I get a string containing the time/date contained."), just tell us what the value is! Contextualise it afterwards if need be. Basically don't second-guess what we're asking for. – Retired

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. – Tiffaneytiffani

As a workaround, can you use a to_char() function in your query? – Vandervelde

OK, that is perhaps significant information to - once again - ADD TO THE QUESTION. What happens if you try yourCol.toString(), then parse it as a date? Or perhaps look for settings against the driver to not return objects, but return just values? – Retired

Checking the driver API is the best bet. However failing that, have you tried converting it to a date in your query? I do not use Oracle, but their docs says it is an extension of date, so their must be some conversion function available, like to_date. As a last resort, the java API suggests theTimeStampCol.dateValue() might work. – Heteropolar

(Edit) Hm... I just noticed you said you tried dateValue() already and said that did not work. IF that is the case, we are back to checking the driver API to see what methods are available. Speaking of which, when you dump the column ... what are the methods available? Because the API suggests dateValue() is one of them. – Heteropolar

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.

Check