[gdal-dev] OCI driver and -lco OVERWRITE=yes

[Rahkonen Jukka]

Hi,

Peter J Halls wrote:
> 
> Jukka,
> 
>     several things here and, I think, they may stem from the 
> Oracle OCI API, rather than the OGR OCI Driver.
> 
>     Firstly, drop table does not remove the index(es): these 
> have to be dropped explicitly.

Really? I made some experiments with SQL*Plus and dropping my spatial
table seems to drop spatial index as well.

>  Secondly, there is no 
> automatic handling of SDO_GEOM_METADATA: 
> again, this must be handled explicitly: the OCI API provides 
> no assistance.

That's true. But if ogr2ogr is going to overwrite some table it should
do delete+insert or alternatively update to SDO_GEOM_METADATA as well or
the result may be unusable. Now it seems to try always with INSERT.

>     Thirdly, so far as I am aware, Oracle names indexes internally.

Sure indexes can, and must be named, like:
CREATE INDEX "TEST_SP" 
ON "TEST"("GEOM") INDEXTYPE IS 
MDSYS.SPATIAL_INDEX PARAMETERS (' TABLESPACE="SP_INDEX"  
LAYER_GTYPE="POINT"')

Ogr2ogr OCI driver seems to give index name for table "TABLE" as
"TABLEI". Our standard is to use TABLE_SP.

>     Fourthly, so far as I am aware, the OGR -lco append and 
> overwrite options enable access to the user's tablespace, 
> rather than to create a new tablespace (which I do not think 
> is supported in the API).  I had not thought of trying to do 
> a delete through OGR, mainly as I use SQL Developer on a 
> regular basis and just do table management there.

I was not trying to create new tablespace, just to use an existing one
that is reserved for indexes. And it works, I can pass tablespace for
spatial index by giving it with ogr2ogr "INDEX_PARAMETERS".  Data goes
always into the ogr2ogr users tablespace and that's OK.

I am dropping tables manually as well, but because ogr2ogr OCI driver
help was advertising that it can overwrite Oracle tables I just had to
try it. It did not work for me and I wonder if I did something wrong or
if OVERWRITE=yes just does not work. In the latter case it might be good
to remove it from driver help.

The correct work flow with OCI driver, if old table should be
overwritten, seems to be:
- drop table "TABLE"
- delete from USER_SDO_GEOM_METADATA where table_name='TABLE'
- run ogr2ogr

-Jukka-

 
> Best wishes,
> 
> Peter
> 
> Jukka Rahkonen wrote:
> > Hi,
> > 
> > I tried to follow the example from http://gdal.org/ogr/drv_oci.html 
> > but but -lco OVERWRITE=yes does not overwrite anything for 
> me. I have 
> > to drop the table manually first. It is not about user 
> rights, I can 
> > drop the table as the same user from SQL*Plus. I quess that 
> > OVERWRITE=yes should drop the table and indexes and delete the 
> > corresponding line from USER_SDO_GEOM_METADATA. However, 
> ogr2ogr with this option gives me just:
> > 
> > FAILED: Layer test already exists, and -append not specified.
> >         Consider using -append, or -overwrite.
> > ERROR 1: Terminating translation prematurely after failed 
> translation 
> > of layer test
> > 
> > I did consider using -overwrite, but it is not supported 
> with OCI driver.
> > Ogr2ogr message:
> >  ERROR 6: DeleteLayer() not supported by this data source.
> > DeleteLayer() failed when overwrite requested.
> > No surprise, I bet that because of this there is the -lco 
> OVERWRITE=yes option.
> > But is fails as well.
> > 
> > Ogr2ogr I am using comes from MS4W 2.2.8.
> > 
> > One question about OCI-driver: Is is possible to give the 
> name of the 
> > spatial index that will be created? I have succeeded in giving the 
> > tablespace for index with INDEX_PARAMETERS but index name is not a 
> > parameter in Oracle CREATE INDEX, it is given before the parameters.
> > 
> > -Jukka Rahkonen-
> > 
> > _______________________________________________
> > gdal-dev mailing list
> > gdal-dev at lists.osgeo.org
> > http://lists.osgeo.org/mailman/listinfo/gdal-dev
> 
> --
> --------------------------------------------------------------
> ------------------
> Peter J Halls, GIS Advisor, University of York
> Telephone: 01904 433806     Fax: 01904 433740
> Snail mail: Computing Service, University of York, 
> Heslington, York YO10 5DD This message has the status of a 
> private and personal communication
> --------------------------------------------------------------
> ------------------
>