Diagnose ESRI-Oracle layer problems - error: no integer rowid or spatial column

[GeoffLau]

A colleague has a new polygon dataset which will not draw in ARCCatalog.

I have subset the dataset to 10 rows in Oracle, created a spatial index, inserted a sdo_geom_metada record with the correct srid and appropriate extents, registered the table with sdelayer (-e a+), and altered the layer to set the projection (-G file=c:\co-ord\gda94.prj) and the envelope (-E calc).

When I open the layer in ARCCatalog nothing displays. When I pan the drawing I get errors like "The selected object failed to draw. (?)The layer(?) has no integer rowid or spatial column". (I don't have access to the work environment at the moment so I'm unsure of the exact wording).

In another test I replaced a geometry in the non-drawing layer with one from another drawable layer using the same projection. In this case the layer displayed the new object but panning the drawing gave a different error "The given coordinate references are incompatible" - which I don't understand as the coordinates, the projection and the srid are the same for both layers.

I've checked the users_sdo_geom_metadata contents, the sdo_geometry srid (82474 in Oracle, 1894 in ESRI sdelayer -o describe_long), the sdo_geometry gtype (2003) and the sdo_geometry elem_info etype (1003). Oracle's validate_layer_with_context reports no errors. In my software environment (SDE 8.3, Oracle 9i, ARCCatalog 9.2) I don't seem to have any ESRI diagnostic tools, I can't find any client-side esri logfiles, and I don't have access to server-side logfiles.

What more can I do to track down the problems with this layer?

[VinceAngelo]

ArcSDE 8.3 and Oracle 9i are both "retired" (unsupported by their manufacturers), so you have
fewer options than most.  What version of Oracle 9i are you using (9.2.x.y)?   Have you applied
the terminal ArcSDE 8.3 Service Pack and patch (SP2 w/ Three Tiered Connection Security Patch )?

Having the exact error message is very useful, but since the error compains about the rowid colum,
'sdetable -o describe' output would be more critical.

- V

[GeoffLau]

Hi Vince,
Thanks for your response. Yes, the versions are old but plans are underway to upgrade.

I've exp/imp'd the table to our dev 11.2 database with sde 9.3 and found new sdelayer -o register errors:
ORA-13000: dimension number is out of range
fixed with
exec sdo_migrate.to_current('ntg_mga3','geometry1') (which changed gtype from 3 to 2003)
and
Error: Coordref or Layer precision not OK for requested operation (-375)
fixed with -P HIGH

I was then able to register and alter the layer, and to confirm that it draws in ArcCatalog, so I'm back to the old versions. The gtype must have been changed in the exp/imp as it is 2003 in the source 9.2 database.

Geoff