[gdal-dev] Convert non-spatial Oracle table to spatial Oracle table in a different database

[Langford, Robert]

All,

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 at 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 at 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.

Regards,

Rob=
DISCLAIMER: The information in this message is confidential and may be legally privileged. It is intended solely for the addressee.

Access to this message by anyone else is unauthorised. If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful.
As a public body, Salford City Council may be required to disclose this email [or any response to it] under the Freedom of Information Act 2000, unless the information in it is covered by one of the exemptions in the Act. 
Please immediately contact the sender if you have received this message in error. 

For the full disclaimer please access http://www.salford.gov.uk/e-mail.  Thank you.