3

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.

Improve this question
4
  • 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. Commented Sep 10, 2014 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. Commented Sep 10, 2014 at 8:55
  • Perhaps running the command with --debug on could give some information about what GDAL is trying to do. Commented Feb 19, 2015 at 14:58
  • Do you have a DBLink between these two databases? If so it may be easier to do this within the database with a materialized view/snapshot. Commented Sep 9, 2015 at 10:14

2 Answers 2

Reset to default
1

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.

Improve this answer
0

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)

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.