Row with nulls in SDE.GDB_ITEMS

110
0
06-28-2022 05:59 PM
Labels (1)
Bud
by
Regular Contributor II

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.

Bud_0-1656463441096.png

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)

 

0 Kudos
0 Replies