Why two tables when using SDO_GEOMETRY?

[Test Sf]

When using the SDO_GEOMETRY storage type, why does dbms2sde create a second table and spatially enables that table (instead of adding the spatial layer to the original table)?

[Mark Ohrenschall]

I am pursuing this issue with SDE developer support at ESRI -- they have confirmed this and have registered it as a bug.

The problem arises (at least superficially) because when SDE adds a spatial layer to a table using the SDO_GEOMETRY storage type it also adds an "objectid" column that is constrained to be "not null."

Of course, anytime you alter a table (with rows) and add a column that is constrained to be "not null" you will get an error. This is the error message I get when I call the SDE API function SE_layer_create on a non-empty table when I've set the storage type to SDO_GEOMETRY:

SE_layer_create encountered a -51 error:
Underlying DBMS error
Extended error code: 1758, extended error string:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

So this forces me to add the spatial layer to an empty table, and then add rows from the original table to the new table as I spatially enable those rows. (I get the same error message when I register a table using sdelayer -o register.)

An intriguing alternative to this is to register the table with ArcSDE first (using ArcCatalog) and then spatially enable the table. However, when I've tried this I cannot see the spatially enabled table with either ArcCatalog or ArcMap.