1

I managed to get the OCI driver to work on both oracle table and views but I can't get it to work with synonyms.

Is this a limitation or am I doing something wrong?

Here's a sample of the command I'm using: 

"C:\Program Files\GDAL\ogr2ogr.exe" -f "ESRI Shapefile" "test_shape.shp" "OCI:<schema>/<password>@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA = (SID =orcl))):MY_LOCAL_SYNONYM

I get the following error:

ERROR 1: ORA-04043: object "MY_LOCAL_SYNONYM" does not exist

If I log into SQL Developer with the same schema/password and try to perform a select it works perfectly.

SELECT * FROM MY_LOCAL_SYNONYM;

SELECT * FROM <schema>.MY_LOCAL_SYNONYM;
Improve this question
4
  • What if you connect without the layer name and use -sql "select * from my_local_synonym"? The OCI driver probably wants to read some metadata from ALL_SDO_GEOMETRY_METADATA and you may need to insert a row for your synonym. – user30184 Dec 15 '16 at 15:07
  • The synonym is listed in the USER_SDO_GEOM_METADATA. I have tried the -sql query as well (with and without schema prefix) without success. – Cosme Benito Dec 15 '16 at 15:29
  • Then it may be that synonyms are not supported. Adding --debug on into your command prints some information about the SQL queries. – user30184 Dec 15 '16 at 15:36
  • It does add some additional info but nothing that's too revealing. The most interesting part is that it after the object not existing error it appears to successfully connect? GDAL: GDALOpen(OCI:<schema>/<schema>@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA = (SID=orcl))):MY_LOCAL_SYNONYM, this=0000000001C39CC0) succeeds as OCI. – Cosme Benito Dec 15 '16 at 15:59
1

Synonyms are just simply not supported by the OCI driver. Then again, hardly any GIS tool understands anything but straight tables. A few understand views. Very few (I don't know of any) understands synonyms.

In the case of GDAL, the OCI driver looks for a table or a view by the name you give, i.e. it searches for a table with that name in the USER_TABLES dictionary view, does not find any, then searches for a view in USER_VIEWS, and again does no find any.

Adding support for synonyms to the OCI driver is probably doable. You need to open a ticket for that at https://trac.osgeo.org/gdal

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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