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?

     i.name as table_name
    ,extractvalue(definition_xml.column_value, 'DEFeatureClassInfo/SubtypeFieldName') as subtype_field_name
    ,it.name || ' [sub type]' as item_type
     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
      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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.