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
    
I have the same problem (GDAL 1.10.0). It seems very unreasonable to me for ogr2ogr to add rows to USER_SDO_GEOM_METADATA when copying data FROM Oracle. Interestingly the problem does not exist when the name of the source table/view is shorter than 10 characters. It must be a bug in GDAL for sure. –  Viktor Rasmussen Sep 10 at 8:27
    
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post - you can always comment on your own posts, and once you have sufficient reputation you will be able to comment on any post. –  Martin Sep 10 at 8:55

2 Answers 2

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

I was having the same (or similar) issue with simply reading from a database. I noticed as well that a non-spatial table was being added to the SDO_GEOM_METADATA table. To resolve it, I removed the table name from the OCI connection string. Since I had the two tables (joining a non-spatial to the spatial for the query) in the SQL, it still worked and I no longer had the trigger going off. I should note I'm running from the C++ GDAL interface.

share|improve this answer

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.