5

I have an oracle table which I would like to query/make a selection. I would then like to export the selection to a shp file.

I'm using gdal and ogr2ogr.

Following the guidelines here and here I have come up with the following:

ogr2ogr -f "ESRI Shapefile" c:/Testing/Scratch/GDAL/symbol.shp OCI:dbusername/db_password@tns_name -sql "SELECT* FROM tablename where VERSION_DATE >= CURRENT_DATE -8" -progress

I then get the following error: "Unable to open datasource 'OCI:dbusername/db_password@tns_name' with the following drivers"

There is then a long list of drivers including "ESRI Shapefile".

I have now added " after OCI as suggested.

ogr2ogr -f "ESRI Shapefile" c:/Testing/Scratch/GDAL/symbol.shp OCI:"dbusername/db_password@tns_name -sql "SELECT* FROM tablename where VERSION_DATE >= CURRENT_DATE -8" -progress

I no longer get the errors about it being unable to open the datasource with the drivers. I now just get an "Access in denied".

I have a working connection to the database in ArcGIS using the same username & password. Does gdal generate its own user?

  • 1
    you could try with a " after OCI: – MickyInTheSky Apr 24 '14 at 9:54
  • Thanks for the quick response Micky. This has given me partial success. I have updated the question with the extra information – Dan_h_b Apr 24 '14 at 10:01
  • that's fine Dan. This syntax works for me: OCI:"dbusername/db_password@tns_name" – MickyInTheSky Apr 24 '14 at 10:04
3

No, gdal doesn't generate any user.

You should find '-> "OCI" (read/write)' in the Supported Formats list if "OCI driver" is installed/configured properly. At this point the following syntax (you have to customize a little bit) should work:

ogr2ogr <srs> -f "ESRI Shapefile" /favourite/path/test.shp OCI:"<oracle user>/<oracle password>@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <port number>)))(CONNECT_DATA = (SID =<sid name>))):<oracle schema>.<oracle table>"
  • So I tried it on another machine and the code worked fine. There was something wrong with the installation on the development box. Thanks to everybody for your help. – Dan_h_b Apr 25 '14 at 10:29
  • This might be a silly question, but how do you find the sid name ? – HC Haase Apr 24 '15 at 9:29
  • Usually you should find it in configuration file as 'tnsname.ora', but you also could take a look at this post. – MickyInTheSky Apr 24 '15 at 9:45
0

The syntax is fine if you have a properly defined tnsnames.ora. This is probably a problem installing a GDAL library. There are multiple versions of GDAL with support for different formats. If OCI is not in the list of drivers, which is listed in error message, then your GDAL library does not work with Oracle database. The simplest solution is install GDAL from project OSGeo4W (https://trac.osgeo.org/osgeo4w) where you can add drivers OCI, FileGDB etc., which are not normally part of library.

  • The tnsnames.ora file is properly defined, as I am able to access the tables from other software using tns entries. OCI is not on the list. The link you've provided is where I installed the software from earlier today. However, when I got to Step 4 of the Quick start guide it gave me no package options. I think I will have to re-install. Is there not a way for me to just install the missing drivers? – Dan_h_b Apr 24 '14 at 10:12
  • Yes, you have to make reinstallation of OSGeo4W. Only select Advance Install and Install from Internet. If you didn't delete source files, that have been downloaded for the last installation, there is only a small addition. Sure you need GDAL-oracle plugin and the OCI from the Libs section – skedge Apr 24 '14 at 11:00
0

It's probably worth adding that if you are applying that SQL to one table in a large database, you can likely speed up the ogr2ogr operation by feeding fake info to the OCI. Let me explain...

Here, I direct the OCI to search for a non-existent table called "junk" in DATABASE
ogr2ogr -a_srs epsg:3005 -f "KML" X:\test.kml OCI:user/password@DATABASE:junk -sql @X:\style.sql -nln "wildfires" -progress

That means ogr looks for tha table "junk", doesn't find it, spits back a couple of error messages, and continues on to my SQL statement, in which I explicitly identify the tables I want .. instead of searching 2000+ tables in the huge database I am accessing..

Here's the error messages you'll get, which are harmless, before the code executes:
ERROR 1: ORA-04043: object junk does not exist in OCIDescribeAny ERROR 1: ORA-04043: object junk does not exist in OCIDescribeAny ERROR 1: ORA-04043: object "junk" does not exist in OCIDescribeAny ERROR 1: ORA-04043: object "junk" does not exist in OCIDescribeAny

That's a bit tangential to your question, but I hope it helps.

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.