Transferring data from PostGIS to Oracle Spatial

Question

I would like transfer spatial data from PostGIS to Oracle Spatial. I want to use Python's modules psycopg2 and cx_oracle. Transfer Oracle > PostGIS is OK, but in opposite version I have problem with a geometry column. The important part is:

cursorP.execute("select fid, kod, st_asBinary(geom) as geom from ruian.vusc_g")
while 1:
    a = cursorP.fetchmany()
    if not a:
        break
    val = list()
    for row in a:
        dictionary = dict()
        dictionary['1']=row[0]
        dictionary['2']=row[1]
        dictionary['3']=cx_oracle.Binary(row[2])
        val.append(dictionary)
    orcl_inserttext = '''INSERT INTO test_Postgres VALUES (:1, :2, SDO_UTIL.FROM_WKBGEOMETRY(:3))'''
    cursorO.executemany(orcl_inserttext,val)

Wihout geometry it's OK. But in this case I receive Error ORA-01461: can bind a LONG value only for insert into a LONG column. Psycopg2 gives me WKB like buffer, but i don't know, how to use it in a cx_oracle insert.

In my opinion geometry is to large for this syntaxe inserting of records.

Do you have any idea how do it differently?

Answer 1

The SDO_UTIL.FROM_WKB_GEOMETRY() function expects a BLOB as input. But it looks like cx_oracle.Binary() returns a LONG. You could try going via WKT instead. The SDO_UTIL.FROM_WKT_GEOMETRY() function takes a string (VARCHAR2) as input or a CLOB.

If this is not a one-off, you may want to consider using ogr2ogr as @Lafleur suggested. Just make sure you use a build that has both a postgis and an Oracle plugin. Note that the Oracle plugin is called "OCI".