I'm trying to use ogr2ogr to convert data from a non-spatial table (has X & Y values) in an Oracle database (1) to a spatial format in a different Oracle database (2). This will be scheduled to run daily to overwrite the table in Oracle database (2) each time.
I have it working using a ogr2ogr's Virtual File Format, with the file
Oracle.vrt which is like:
<OGRVRTDataSource> <OGRVRTLayer name="TEST_X_Y"> <SrcDataSource>OCI:userid_1/password_1@database_instance_1:TEST_X_Y</SrcDataSource> <SrcLayer>TEST_X_Y</SrcLayer> <GeometryType>wkbPoint</GeometryType> <LayerSRS>EPSG:27700</LayerSRS> <GeometryField encoding="PointFromColumns" x="X_TEXT" y="Y_TEXT"/> </OGRVRTLayer> </OGRVRTDataSource>
Then using the following ogr2ogr command:
ogr2ogr -update -overwrite -f OCI OCI:userid_2/password_2@database_instance_2:TEST_OGR_IN Oracle.vrt TEST_X_Y -lco OVERWRITE=YES -lco SRID=27700 -nln TEST_OGR_IN
Now this works and creates the spatial table in the Oracle database (2) fine, but when I run the ogr2ogr command a second time I get an error like:
ERROR 1: ORA-00001: unique constraint (MDSYS.UNIQUE_LAYERS) violated ORA-06512: at "MDSYS.SDO_GEOM_TRIG_INS1", line 43 ORA-04088: error during execution of trigger 'MDSYS.SDO_GEOM_TRIG_INS1' in INSERT INTO USER_SDO_GEOM_METADATA VALUES ('TEST_X_Y', ...
The table (
TEST_OGR_IN) still gets overwritten/updated in the Oracle database (2) but the problem appears to be that ogr2ogr has inserted values into the
USER_SDO_GEOM_METADATA table on the Oracle database (1) for the non-spatial table (
TEST_X_Y) and is violating an index/constraint?
Although the process works, I'd like to understand why the error is happening and how I can avoid it.