How to check "One or more layers Failed to draw. Underlying dbms error" ?

[tongkimongki]

Dear experts,

I loaded SDO_Geometry type data directly from oracle 10gR2.. Then I open some feature classes via arcmap. I pan around here and there and suddenly when i pan to this particular area  I encounter error "One or more layers Failed to draw. Underlying dbms error". Only when I pan to this particular area gives me the error. Some feature classes dont have this error.

There are many feature classes (hundreds of them). How do i make sure that all the data are free of this kind of error ? I dont want to load all feature classes one by one and panning around to test it. Is there a way to find out this error automatically (or at least without checking them one by one and panning around) ?

My environment : oracle 10gr2, arcgis 9.2 sp6, arcsde 9.2 sp6. I load the data via oracle and register them manually

many thanks

[VinceAngelo]

Have you looked in the error logs of both ArcSDE and Oracle?

If the error is invalid geometry, the only way to detect that is to run queries against each table.
The 9.0 API introduced a "invalid_shape_mode" flag to streams, allowing the API to pass back
invalid geometry instead of crashing the stream, but ArcGIS doesn't use this (probably because
it needs valid geometry to render anyway). I added a hidden "+INVALIDSHAPE" flag to the
'sdequery' utility of se_toolkit, but it's not as useful as it might be, since it doesn't capture just
the invalid shapes (it just adds "Invalid" before the shape type -- e.g., "Shape: Invalid Line
shape (3 parts, 206 vertices)". Yet you could still scan all the layers, looking for 'Invalid', and
then do further research on the ones that had hits:

#!/bin/sh
SDEUSER=browse_user
SDEPASSWORD=password
export SDEUSER SDEPASSWORD
for layer in `sdelist -o layer`
do
        count=`sdequery -C shape -l $layer -\# 999999999 +INVALIDSHAPE | grep Invalid | wc -l`
        test $count -gt 0 && echo $layer $count
done

I'll add doing something more useful with SE_stream_set_invalid_shape_mode to my
"SDEQUERY_TO_DO" list.

- V

PS: Oracle has a validation function for SDO_GEOMETRY;  that won't necessarily catch
all the topology errors that ArcSDE will catch, but it would be a good start.

[TravisVal]

When I try to diagnose a problem like this I usually start, as V has already suggested, by looking through the logs.  There should be some information in the SDE logfiles, but it can sometimes be had to find.

Failing any answers there, I move on to running a Oracle validation procedure on the source table.  The error codes returned are Oracle errors, so to look them up you can use Google, Oracle doc, however you would normally. 

SQL> SELECT A.ObjectID, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(A.SHAPE, M.diminfo) AS PROBLEM
  2  FROM SDO_TEST A,USER_SDO_GEOM_METADATA M
  3  WHERE M.table_name = 'SDO_TEST'
  4  AND M.column_name = 'SHAPE'
  5  AND SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(A.SHAPE, M.diminfo) <> 'TRUE';

  OBJECTID
----------
PROBLEM
--------------------------------------------------------------------------------
        20
13356 [Element <1>] [Coordinate <3>][Ring <2>]

        22
13343 [Element <1>] [Ring <2>]

        23
13349 [Element <1>] [Ring <1>][Edge <3>][Edge <1>]

Then, I'll run SDELayer -o feature_info ... -r invalid.  This will tell me what rows are invalid from SDE�??s point of view.  The rows that are returned will have SDE errors, but the actual error maybe incorrect.  Also, there isn�??t always a 1:1 correlation between the invalid geometries returned by Oracle and those returned by SDE (rows 20 and 21 for example).

sdelayer -o feature_info -l SDO_TEST,shape -u map -i 5151 -s test -r invalid

ArcSDE 10.0  for Oracle11g Build 685 Fri May 14 12:05:43  2010
Layer    Administration Utility
-----------------------------------------------------
Row Id,FID,Entity Type,Annotation,Cad Data,Number of Points,Number of Parts,Number of Subparts,Self-Touching Rings,Minim
um Precision,Verification

21,21,A,F,F,8,0,0,F,Basic,-150
22,22,A,F,F,8,0,0,F,Basic,-148
23,23,A,F,F,5,1,1,F,Basic,-152

Total rows examined:          20
Total invalid shapes:          3

You can look the actual errors up using the online documentation (here is the 9.3 version - http://edndoc.esri.com/arcsde/9.3/api/capi/returncodes_incl.htm) or you can use SDELayer -o list -v OID for the row you are investigating. Unlike SDELayer -o feature_info, -o list should return the correct error code. 

sdelayer -o list -l SDO_TEST,shape -u map -i 5151 -s test -v 23


ArcSDE 10.0  for Oracle11g Build 685 Fri May 14 12:05:43  2010
Layer    Administration Utility
-----------------------------------------------------
Error: Polygon shell has no area (-152).
Error: Cannot fetch row for layer

At this point, to get the feature class to draw without errors, you will either have to fix or remove the rows you have found.  I would suggest trying to fix the problem, but that is sometimes easier said then done. 

Let us know how it goes.

[tongkimongki]

Thanks Vince, Thanks Travis,

I will try yours suggested solution... Thanks.
I forgot to tell you that the error is gone when i export the feature class to another feature class (still in the same database). The exported feature class is just fine and normal, without the panning error..
Can you explain why the error suddenly disappear after the export ?

Thanks again

[tongkimongki]

Dear expert,

I have done the checking using these commands below :

1. sdelayer -o feature_info -l FC1,shape -u user -p user1 -i 5151 -s myserver -r invalid

2. SELECT A.FID, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(A.SHAPE, M.diminfo) AS PROBLEM
FROM SCHEMA.FC1 A,USER_SDO_GEOM_METADATA M
WHERE M.table_name = 'SCHEMA.FC1'
AND M.column_name = 'SHAPE'
AND SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(A.SHAPE, M.diminfo) <> 'TRUE';

I found NO ERROR. But when i pan-ed to particular area it gave me an "UNDERLYING DBMS ERROR [state_id=2461]". And I didnt find any objects with state_id = 2461...

Can some1 give an explanation or suggestion ?

Thanks alot

😄