Oracle - Underlying DBMS error [ORA-01722]

[robert_at_work]

Hi there,

our data is stored in an Oracle-SDE database. I created a table in Oracle directly (joining tables as CTAS). As result of the join some feature may or may not have a geometry.

The table shows up in ArcCatalog or in "Add Data" in ArcMap (10.7.1) immediatley.

When added to ArcMap no issues / error messages occur. Features are shown as expected.

If I register the table first in the geodatabase (through ArcCatalog), and add the table to ArcMap no features will be drawn and the error message Underlying DBMS error [ORA-01722] appears.

To me it looks like the error message is caused by the fact that some feature do not have a geometry (ArcMap displays the error message in full - the query in question also contains a WHERE clause with "SDE.ST_EnvIntersects(ELE_VM_KABEL_WO.SHAPE,:1,:2,:3,:4) = 1" - I think the bind variables :1-:4 have invalid values (but not <null> - <null> seems to work if used as values) and cause the error message)).

My questions are:

- can someone confirm this or is something else causing the error message?

- if a table/view is not registered - do ArcMap/ArcGISServer not use a spatial filter at all or do they use a spatial filter in a different way so that a non-existing geometry is not causing any trouble?

Reading through the docs I'm not sure if there is a performance advantage using a registered view/table over a non-registered table/view?

I know that I could filter out features without geometry but it's not a good solution as they won't show up in a search then.

Thank you, Rob

[MarceloMarques · Accepted solution]

Hello,

- can someone confirm this or is something else causing the error message?

[Marcelo]: did you create a spatial index for the featureclass ???
Add Spatial Index (Data Management)—ArcMap | Documentation (arcgis.com)

Note: ORA-01722 error can have many causes, I suggest you open a ticket with Esri Support to troubleshoot it further.

- if a table/view is not registered - do ArcMap/ArcGISServer not use a spatial filter at all or do they use a spatial filter in a different way so that a non-existing geometry is not causing any trouble?

[Marcelo]: you need to create a spatial index, then ArcGIS can use the spatial index in spatial filtering operations, like rendering the data on the map at different scales.

Reading through the docs I'm not sure if there is a performance advantage using a registered view/table over a non-registered table/view?

[Marcelo]: if you need to edit the data in ArcMap and if you need advanced geodatabase functionality e.g. arcgis topology, geodabase versioning, geodatabase archiving, etc. then the data must be registered with the geodatabase, from a performance standpoint the arcmap will behave differently when the data is registered with the geodatabase and when is not registered, arcmap sents different SQL queries to the rdbms, hence performance will depend on a series of other factors besides simply if the data is registered or not with the geodatabase, rdbms performance depends on the disk storage, and rdbms server cpu/memory, also if the database tables have indexes and statistics updated.