[cx-oracle-users] Problem with SDO_GEOMETRY gettype and executemany

[Pavel S. <pav...@ho...>]

Hi everybody,

I been trying to use cx_Oracle with the newest gettype feature together with "executemany" function (version 5.2.1 built from the bitbucket repository). For some reason after creating around 1000 objects (stored in a list) the performance start to degrade, taking approximate half of a second to create a single object, I am not sure what is the reason behind this behavior.

Below is code I try to execute:

#------------------------------------------------------------------------------
# InsertGeometry.py
#   This script demonstrates the ability to create Oracle objects (this example
# uses SDO_GEOMETRY) and insert them into a table.
#------------------------------------------------------------------------------

from __future__ import print_function

from random import randint
import cx_Oracle
import time

# create and populate Oracle objects
connection = cx_Oracle.Connection('[your db here]')
#print(connection)
#print(dir(connection))

# create table, if necessary
cursor = connection.cursor()
cursor.execute("""
        select count(*)
        from user_tables
        where table_name = 'TESTGEOMETRY2'""")
count, = cursor.fetchone()
if count == 0:
    print("Creating table...")
    cursor.execute("""
            create table TestGeometry2 (
                IntCol number(9) not null,
                Geometry SDO_GEOMETRY not null
            )""")

# remove all existing rows and then add a new one
print("Removing any existing rows...")
cursor.execute("delete from TestGeometry2")
print("Adding row to table...")

geometries = []
typeObj = connection.gettype("MDSYS.SDO_GEOMETRY")
elementInfoTypeObj = connection.gettype("MDSYS.SDO_ELEM_INFO_ARRAY")
ordinateTypeObj = connection.gettype("MDSYS.SDO_ORDINATE_ARRAY")

start_time = time.time()
for i in xrange(0, 4000):
    print(i)
    ordinates = [randint(1,9) for t in xrange(0, 30)]
    geom = typeObj.newobject()
    geom.SDO_GTYPE = 2003
    geom.SDO_ELEM_INFO = elementInfoTypeObj.newobject()
    geom.SDO_ELEM_INFO.extend([1, 1003, 3])
    geom.SDO_ORDINATES = ordinateTypeObj.newobject()
    geom.SDO_ORDINATES.extend(ordinates)
    geometries.append((i, geom))

cursor.executemany("insert into TestGeometry2 values (:1, :2)", geometries)
connection.commit()
elapsed_time = (time.time() - start_time)
print(elapsed_time)
print("Success!")

Thanks,
Pavel Simo