6

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?

Improve this question
3
  • 1
    you could try with a " after OCI:
    – MickyInTheSky
    Commented Apr 24, 2014 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
    Commented Apr 24, 2014 at 10:01
  • that's fine Dan. This syntax works for me: OCI:"dbusername/db_password@tns_name"
    – MickyInTheSky
    Commented Apr 24, 2014 at 10:04

3 Answers 3

Reset to default
4

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>"
Improve this answer
3
  • 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
    Commented Apr 25, 2014 at 10:29
  • This might be a silly question, but how do you find the sid name ?
    – HC Haase
    Commented Apr 24, 2015 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
    Commented Apr 24, 2015 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.

Improve this answer
2
  • 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
    Commented Apr 24, 2014 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
    Commented Apr 24, 2014 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.

Improve this answer
3
  • Have you find a method in order to not show the ERROR 1: ORA-04043 message?
    – Roberto Marzocchi
    Commented Jun 11, 2020 at 12:46
  • No - this error always shows, but never actually causes a problem.
    – grego
    Commented Jun 12, 2020 at 23:09
  • ok. Me too. Thanks for your feedback
    – Roberto Marzocchi
    Commented Jun 16, 2020 at 6:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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