Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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. Many thanks for any assistance offered.

share|improve this question
add comment

1 Answer

I don't know ogr so am just approaching this from my (limited) Oracle Spatial perspective.

It looks like you have a issue with a built in trigger - MDSYS.SDO_GEOM_TRIG_INS1. For me that trigger looks like:

create or replace
TRIGGER SDO_GEOR_TRIG_INS1
INSTEAD OF INSERT ON user_sdo_geor_sysdata
FOR EACH ROW
BEGIN
  SDO_GEOR_INT.insertUserSysEntry(user, :new.table_name, :new.column_name,
      :new.metadata_column_name, :new.rdt_table_name, :new.raster_id,
      :new.other_table_names);
END;

There's a post about ORA-04088 here that may help resolve it - http://www.dba-oracle.com/t_ora_04088_error_during_execution_of_trigger_string_string.htm

You could try disabling the trigger possibly for this insert then re-enabling it afterward.

You can also manually edit USER_SDO_GEOM_METADATA after the fact to remove the undesired entry.

My own guess would be that your OGR isn't providing sufficient information about the non-spatial data for the trigger to be able to fill the new entry properly.

(StackOverFlow has a lot of Oracle experts if this rather shaky answer doesn't help, its where I ask my Oracle questions)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.