Shouldn't ODBC be transparent ?
Norbert Klamann, May 23, 2006 - 11:49 am UTC
Thanks a lot for the fast answer, Tom !
I fear that 'to_char' is no option for us because we have no control over the SQL which the application builds.
From my point of view this shows that these not so very new datatypes are not supported by the Oracle Software.
A colleague of mine placed a TAR in this matter.
May 23, 2006 - 3:48 pm UTC
guess the question would be: you are using the latest software right.
Use a view
jim, May 23, 2006 - 1:52 pm UTC
You can create a view with the to_char and access the view. You could even rename the original table and give the view the name of the original table.
"control" or "no control" on the app?
Gabe, May 23, 2006 - 3:45 pm UTC
If one is to do the view/table switch then the view should have a cast(ts_ltz as date) rather than a to_char()
the datatype should be kept to what is used to be.
Nonetheless, I dont quite understand why one disassociates the tables, or any other database objects, coming with a 3rd part application from the application itself
change any bit on a 3rd party app and all bets are off.
That aside though, the issue/question seems to come down to the ODBC driver supplied by Oracle supporting the TIMESTAMP datatype
</code>
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b15658/app_odbc.htm#sthref971 <code>
does seem to suggest that Oracles ODBC driver can handle TIMESTAMPs !
May 24, 2006 - 6:55 am UTC
the datatype is changing here - to a date, and you lose the timezone information.
TIMESTAMP is not TIMETAMP WITH (LOCAL) TIMEZONE
Norbert Klamann, May 24, 2006 - 1:42 am UTC
These are different types alltogether.
Norbert
I apologize if I was too blunt
Gabe, May 24, 2006 - 12:42 pm UTC
<quote>the datatype is changing here - to a date, and you lose the timezone information.</quote>
They have a 3rd party app - cannot be modified - which has been coded to expect a DATE
would the app magically start handling timezone information just because they changed the datatype in the table? My view is that, changing the SQL datatype wont benefit this app at all [cannot lose something which cannot be had]
so Im assuming the benefit will be for some other applications using the table.
As for the Oracle ODBC driver not working with TIMESTAMP WITH [LOCAL] TIME ZONE
the driver implements a certain spec
as far as I know the ODBC datatype is SQL_TIMESTAMP (a typedef struct in some C header file which goes as detailed as fractions of second)
even if Oracle also mapped its datatypes TIMESTAMP WITH [LOCAL] TIME ZONE to SQL_TIMESTAMP (which I dont know if it does
or if an implicit conversion to TIMESTAMP takes place
and that ora-00932 suggests it doesnt) the ODBC code would still not be able to benefit from the TIME ZONE part. I might be wrong ...
May 25, 2006 - 7:18 am UTC
You know, I didn't read that well enough.
What they are trying to do is futile then - they cannot change a datatype like that - the application isn't coded to deal with it - with the timezone, with maintaining the timezone - with doing the right thing.
sorry about that.
I think the application may benefit from this Datatype
Norbert Klamann, May 26, 2006 - 9:28 am UTC
A TIMESTAMP WITH LOCAL TIMEZONE is autmatically transposed to the local timezone of the user - as defined at the session level.
The time zone is not visible but of course it exists. As I understand it is stored relative to the DB-Parameter TIME ZONE. Because it is not visible the Column looks like normal TIMESTAMP Column and can be used as such.
At the moment we do some tests with the ODBCD-Driver of Datadirect.com and they look promising.
Su Baba, June 20, 2006 - 2:37 pm UTC
We have the requirements to access columns with TIMESTAMP WITH TIMEZONE datatype through an Oracle ODBC driver. We can read the data through a view that uses TO_CHAR() to convert the TIMESTAMP WITH TIMEZONE columns. However, is there any way we can write to the table through the same view?
June 21, 2006 - 9:39 am UTC
much much rather use a procedure, why not use a stored procedure?
and you need not use a view - you could just use to_char in the application in the first place.
you could code instead of triggers on a view.
OLE DB may help
David Penington, July 11, 2006 - 9:47 pm UTC
The Oracle OLE DB provider includes better support for TIMESTAMP WITH LOCAL TIME ZONE than the Oracle ODBC driver.
I have used the OLE DB provider succesfully with Microsoft DTS to load TIMESTAMP WITH LOCAL TIME ZONE from other vendor Date columns. When I used the ODBC driver for the same task it raised exceptions with the ODBC driver and wouldn't display their data type. The Oracle OLE DB provider both transferred the data and displayed the data type as TIMESTAMP.
I recommend you see if your 3rd party application can use an OLE DB provider. OLE DB is the newer Microsoft technology and supercedes ODBC.
The lack of Oracle ODBC driver support for TIMESTAMP WITH TIME ZONE etc is a known enhancement request 3121786 in the Oracle bug database, but it does not seem to be scheduled for action.