I try to migrate a spatial database from Oracle to Postgresql and fail.
I tried many different ways, as you can see from my previous question here, and no one works. Can someone please tell me about a relatively painless way to do this, as I now don’t know this.
I tried using third-party software such as SwisSQL, but this failed with many errors. I tried creating files with insertion instructions, and then I created a C # program to parse them and replace oracle spatial types with relevant postgis messages and which were not executed with memory deletion from the most basic substitutions due to the huge size of these files. Some of the tables contain more than 2 million records, so you can imagine the size of the file containing the inserts for each of them.
I am very desperate for a solution to this, as it seriously impedes progress on this project. I need data in Postgresql because I write software that should be database agnostic, which means that it needs to be checked against real data in each database.
Any ideas would be welcome with open arms. If this were not for the spatial aspect of this, it would be much simpler and most likely done by now.
EDIT:
Here is an example of an insert created by Toad for Oracle to give an idea of data complexity.
Insert into CLUSTER_1000M
(CLUSTER_ID, CELL_GEOM, CELL_CENTROID)
Values
(4410925,
"MDSYS"."SDO_GEOMETRY"(2003,81989,NULL,
"MDSYS"."SDO_ELEM_INFO_ARRAY"(1,1003,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),
"MDSYS"."SDO_ORDINATE_ARRAY"(80000,103280,81000,104280,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)),
"MDSYS"."SDO_GEOMETRY"(2001,81989,
"MDSYS"."SDO_POINT_TYPE"(80500,103780,NULL),NULL,NULL));