Export Oracle Table Query to ESRI Shapefile or FGDB?

Question

I have created a query and some joins in Oracle SQL Developer on some spatial tables. I would like to export the query result to either a shapefile or File geodatabase. Is this possible?

I have access to FME which I assume will do the trick, but I have never used it before and can't seem to find any documentation to show how this would work.

mattias_j · Answer

Does your query result contains different geometry types (points/polylines/polygons)?
Oracle supports tables/views/querys with different types, but ArcGIS does not, and will only include rows with one of the geometries.

ArcGIS also need a unique identifier field, but can create one of it's own of several fields that together is a unique identifier. When you pasted your query, check "Show advanced options" och click next. Check the field or fields for the unique identifier. And make sure those fields don't contain any null values.

MikeMacRae · Answer

Thanks Mattias,

I tried the Query layer function. I compared my results from the same query in Oracle. Using the query in Oracle, I get about 9000 records or so. When I copy and paste the same query into the Query Layer dialogue after I connect to my database, I get about 7800 records. I'm expecting to get the exact same results. Not sure why I am not...

mattias_j · Answer

You can probably do that with FME, but you can also do that easy with ArcMap (version 10.0 and higher).
Have you used Query layers? you can use your queries and and them as a layer in ArcMap and then you can export them to shapefile or FGDB as usual by right clicking the layer and choose Data -> Export data...

Read this about creating Query layers:

ArcGIS Help (10.2, 10.2.1, and 10.2.2)

You're gonna need a 32-bit oracle client installed to be able to connect to oracle from ArcMap which is 32-bit. This was an issue for me at first when I only had a 64-bit oracle client installed.

I can also recommend the GeoRaptor extension to Oracle SQL Developer when working with spatial data. With GeoRaptor you can import and export shapefiles from Oracle SQL Developer.

Spatial Viewer for Oracle SQL Developer download | SourceForge.net