mxODBC / Oracle / ORA-01475 Error

[M.-A. Lemburg]

Stefan Kirchberg wrote:
> 
> Hi all,
> I'm trying to insert some rows into a table as follows:
> 
> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> import  mx.ODBC.Windows as      ODBC
> dsn="DSN=STARFMDS;UID=system;PWD=manager"
> db = ODBC.DriverConnect(dsn)
> db.bindmethod = ODBC.BIND_USING_PYTHONTYPE
> c = db.cursor()
> c.execute("drop table t")
> c.execute("create table t (a int)")
> 
> rows = [ (0,), (None,) ]
> c.executemany("insert into t(a) values (?)", rows)
> db.commit()
> 
> c.execute("select * from t")
> print c.fetchall()
> <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
> 
> The program crashes executing the INSERT statement with the second
> row (as one can see when replacing the executemany() with a
> for loop / execute() structure).
> 
> Oracle says ORA-01475, 'must reparse cursor to change bind variable
> datatype'.
> 
> Obviously the None value is the reason. But I cannot predict if I
> will insert None values (which should mean NULL fields in the database)
> -- it should work anyway.
> 
> It doesn't matter if the program is running with BIND_USING_PYTHONTYPE
> or BIND_USING_SQLTYPE. But BIN_USING_SQLTYPE should work since the ODBC
> module should look into the table to see what type is expected by the
> database, and treat the None value/type correctly.
> 
> Inserting a single row (or multiple identical rows) containing of a None
> value works in the expected way, mixing None and numerical values is
> the problem.
> 
> Any hints?

Which ODBC driver are you using to talk to the Oracle database ?
If it's the Oracle one, please try using the Microsoft replacement
which comes with the MSDAC toolkit from Microsoft.

-- 
Marc-Andre Lemburg
CEO eGenix.com Software GmbH
______________________________________________________________________
Consulting & Company:                           http://www.egenix.com/
Python Software:                        http://www.lemburg.com/python/