I have tables and feature classes in an Oracle 12c geodatabase (10.7.1).
I would like to generate a list of tables and feature classes that have subtypes defined in them.
Is there a way to do this with SQL?
select i.name as table_name ,extractvalue(definition_xml.column_value, 'DEFeatureClassInfo/SubtypeFieldName') as subtype_field_name ,it.name || ' [sub type]' as item_type from sde.gdb_items_vw i inner join sde.gdb_itemtypes it on i.type = it.uuid cross join table(xmlsequence(xmltype(definition).extract('/DEFeatureClassInfo'))) definition_xml where extractvalue(definition_xml.column_value, 'DEFeatureClassInfo/SubtypeFieldName') is not null --and i.name = 'YOUR_OWNER.YOUR_FEATURE_CLASS' and rownum <= 109
Note: The XML in the GDB_ITEMS_VW seems to have problems.
The query above works for the first 109 rows. But fails if 110 rows are returned:
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
I don't know what the cause is. Maybe it just doesn't like the last record in the result set.