Retrieving an Oracle timestamp using Python's Win32 ODBC module

Question

Given an Oracle table created using the following:

CREATE TABLE Log(WhenAdded TIMESTAMP(6) WITH TIME ZONE);

Using the Python ODBC module from its Win32 extensions (from the win32all package), I tried the following:

import dbi, odbc

connection = odbc.odbc("Driver=Oracle in OraHome92;Dbq=SERVER;Uid=USER;Pwd=PASSWD")

cursor = connection.cursor()
cursor.execute("SELECT WhenAdded FROM Log")

results = cursor.fetchall()

When I run this, I get the following:

Traceback (most recent call last):
...
    results = cursor.fetchall()
dbi.operation-error: [Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: expected %s got %s 
in FETCH

The other data types I've tried (VARCHAR2, BLOB) do not cause this problem. Is there a way of retrieving timestamps?

Answer 1

My solution to this, that I hope can be bettered, is to use Oracle to explicitly convert the TIMESTAMP into a string:

cursor.execute("SELECT TO_CHAR(WhenAdded, 'YYYY-MM-DD HH:MI:SSAM') FROM Log")

This works, but isn't portable. I'd like to use the same Python script against a SQL Server database, so an Oracle-specific solution (such as TO_CHAR) won't work.

Answer 2

I believe this is a bug in the Oracle ODBC driver. Basically, the Oracle ODBC driver does not support the TIMESTAMP WITH (LOCAL) TIME ZONE data types, only the TIMESTAMP data type. As you have discovered, one workaround is in fact to use the TO_CHAR method.

In your example you are not actually reading the time zone information. If you have control of the table you could convert it to a straight TIMESTAMP column. If you don't have control over the table, another solution may be to create a view that converts from TIMESTAMP WITH TIME ZONE to TIMESTAMP via a string - sorry, I don't know if there is a way to convert directly from TIMESTAMP WITH TIME ZONE to TIMESTAMP.