CODEWITHSUNDEEP
pythonoracle-databasecx-oracle
bertday
bertday

Reputation: 10971

Bulk insert with cx_Oracle: inconsistent data types

I'm trying to load a large number of records into an Oracle DB using Python and cx_Oracle. The consensus seems to be that you should prepare a cursor and executemany against a list of rows (per this post). So my code looks like:

stmt = "INSERT INTO table (address, shape) VALUES (:1, :2)"
cursor.prepare(stmt)
rows = []
# Make huge list of rows
cursor.executemany(None, rows)

The values I'm passing in look like this:

['1234 MARKET ST', "SDE.ST_Geometry('POINT (0 0)', 2272)"]

The problem is that the SDE.ST_Geometry() database function is being treated as a literal string rather than being evaluated, so I get a cx_Oracle.DatabaseError: ORA-00932: inconsistent datatypes: expected SDE.ST_GEOMETRY got CHAR.

Is it not possible to pass in database functions to a prepared cursor with cx_Oracle?

Upvotes: 0

Views: 1017

Answers (2)

bertday
bertday

Reputation: 10971

In the end I got around this by embedding the function call in the prepared statement:

stmt = "INSERT INTO table (address, shape) VALUES (:1, ST_Geometry(:2, 2272))"

Hat tip to PatrickMarchand for the hint.

Upvotes: 0

Christopher Jones
Christopher Jones

Reputation: 10506

The brief answer is that since you are passing a string, it is treated as a string. Bind values are only every treated as data.

But look at the not-yet released cx_Oracle main line https://bitbucket.org/anthony_tuininga/cx_oracle? It has new object support.

And look at this commit "Added example for creating SDO_GEOMETRY.": https://bitbucket.org/anthony_tuininga/cx_oracle/commits/2672c799d987a8901ac1c4917e87ae4101a1d605

Upvotes: 1

Related Questions