Row with nulls in SDE.GDB_ITEMS

Bud — Wed, 29 Jun 2022 05:32:50 GMT

Oracle 18c 10.7.1 GDB:

I noticed that our SDE.GDB_ITEMS table has a row that is mostly null values. I discovered that row using this technique: Find problem rows in GDB_ITEMS_VW.

That null row causes problems when extracting data from the XML DEFINITION clob column in GDB_ITEMS_VW. For example, these two queries would error-out if I selected ALL rows using CTRL+END in SQL Developer (originally, the queries didn't have WHERE clauses):

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00007: unexpected end-of-file encountered
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
31011. 00000 - "XML parsing failed"
*Cause: XML parser returned an error while trying to parse the document.
*Action: Check if the document to be parsed is valid.

Now that I know what row is causing the problem, it's easy workaround the issue by adding a WHERE clause to the queries:

where 
    i.name is not null

With that said, I'm still curious how that junk row got created. I doubt anyone meddled with the system tables through the back-end using SQL. My best guess is that the row was somehow created via an ArcGIS bug. I looked at some old backups, and it would seem that row has been there for years — since the 10.3.1 GDB days. It wasn't created recently.

Questions:

Has anyone else had that issue? Does the query below find any null rows in your Oracle GDB?

select
    *
from
    sde.gdb_items
where
    name is null

OBJECTID  UUID          TYPE          NAME    PHYSICALNAME  PATH  URL     PROPERTIES  DEFAULTS  DATASETSUBTYPE1  DATASETSUBTYPE2  DATASETINFO1  DATASETINFO2  DEFINITION  DOCUMENTATION  ITEMINFO  SHAPE   CONTINGENTVALUES            
--------  ------------  ------------  ------  ------------  ----  ------  ----------  --------  ---------------  ---------------  ------------  ------------  ----------  -------------  --------  ------  ----------------
3367      {39F456FC...  {F3783E6F...  (null)  (null)        \     (null)  1           (null)    (null)           (null)           (null)        (null)        (null)      (null)         (null)    (null)  (null)

topic Row with nulls in SDE.GDB_ITEMS in Geodatabase Questions

Row with nulls in SDE.GDB_ITEMS