CODEWITHSUNDEEP
pythonoracle-databasegeospatialcx-oraclesdo
ajd018
ajd018

Reputation: 171

select oracle geospatial data to insert geometry into a different table

I am trying to select sdo_geometry out of one table and insert it into another. Having some trouble with how to select the geometry. If I remove the geometry column my code works. So I assume my trouble is there.

import cx_Oracle

connection_src = cx_Oracle.connect('source connection string')
cursor_src = connection_src.cursor()
cursor_src.arraysize = 500
cursor_src.execute("""select ID, MDSYS.SDO_UTIL.TO_WKTGEOMETRY(Geometry_column) from src_schema.src_table""")

connection_dst = cx_Oracle.connect('destination connection string')
cursor_dst = connection_dst.cursor()

rows = cursor_src.fetchmany()
cursor_dst.executemany("INSERT INTO dest_schema.dest_table (ID,Geometry_column) VALUES (:1,:2)",rows)

cx_Oracle.DatabaseError: ORA-00932: inconsistent datatypes: expected MDSYS.SDO_GEOMETRY got CLOB

if I use this instead: MDSYS.SDO_UTIL.EXTRACT(GEOLOC,1) I get this error

Errors in file :
OCI-21500: internal error code, arguments: [kgepop: no error frame to pop to], [], [], [], [], [], [], []
OCI-30757: Message 30757 not found;  product=RDBMS; facility=OCI

Upvotes: 0

Views: 87

Answers (1)

MT0
MT0

Reputation: 168232

You convert it from an SDO_GEOMETRY to a CLOB using the MDSYS.SDO_UTIL.TO_WKTGEOMETRY function.

When you want to reverse it convert it from a CLOB back to SDO_GEOMETRY using the MDSYS.SDO_UTIL.FROM_WKTGEOMETRY function.

import cx_Oracle

connection_src = cx_Oracle.connect('source connection string')
cursor_src = connection_src.cursor()
cursor_src.arraysize = 500
cursor_src.execute(
    "select ID, MDSYS.SDO_UTIL.TO_WKTGEOMETRY(Geometry_column)"
    " from src_schema.src_table",
)

connection_dst = cx_Oracle.connect('destination connection string')
cursor_dst = connection_dst.cursor()

rows = cursor_src.fetchmany()
cursor_dst.executemany(
    (
        "INSERT INTO dest_schema.dest_table (ID,Geometry_column)"
        " VALUES (:1,MDSYS.SDO_UTIL.FROM_WKTGEOMETRY(:2))"
    ),
    rows,
)

Upvotes: 0

Related Questions