ORA-00936: missing expression error

[JimO_Leary1]

I am using ArcMap 10.2 and have connected to my Oracle database, and can see the tables in Catalog. However, when I try to add a layer I get "Could not add the specified data object to the map. Underlying DBMS error[ORA-00936: missing expression]"

I am able to add this Oracle layer to other GUIs such as FME Data Inspector and QGIS. I have added both spatial and non-spatial indexes and have a primary key.

I have been able to connect to and show layers in other Oracle databases in our network, just not from this database. I connected to this database as a user that has select, update, delete, and insert permissions.

Any suggestions?

Thanks

[Anonymous User]

Hi Jim,

This error message typically pops up if there are some parameters or statements missing from a SQL query expression:

Oracle/PLSQL: ORA-00936

It can be related to how a layer was created, whether it participates in a join, and what kind of storage type it is using. Is this happening for any of your other datasets? Is the layer you mentioned a view, a query layer, a joined table, anything other than a simple SDE feature class? Is it versioned?

What happens if you export this feature class to a File GDB - can you add it to the map document?

Finally, what's the storage type - SDELOB or SDOGEOMETRY?

You could try running a SQL query directly on the base table of the layer in SQLplus or Developer and see what happens.

- Julia L.

[JimO_Leary1]

The layer is a table in Oracle Spatial, and is not joined to any other table, and is not a view. It is not versioned.

The Data Type for the geometry column is SDO_GEOMETRY. When I execute the following query:

select t.x, t.y

from fire_stations,

table(sdo_util.getvertices(geom)) t

...I can see x and y rows for each feature. However, when I use ArcMap 10.2 to export the table into a file GDB, I cannot add the file GBD table to ArcMap, and when I try to view it in FME Data Inspector, FME tells me that it has no geometry.

One difference between this database and the databases for which I can add features to ArcMap is that the tables in this database were created by FME. However, FME is very sophisticated and should take care of all details needed when creating a spatial table. For example, it correctly inserts a record into ALL_SDO_GEOM_METADATA.

So I don't know what the difference is. Thanks for your help.

[Anonymous User]

Hey Jim,

Have you renamed the database lately? Does the database name have any spaces in it?