Select all annotation classes with sql in an Oracle SDE

Question

How can I select all annotation classes with sql in an Oracle SDE? I can get all feature classes (including annotation) with this:

select i.name, i.physicalname, i.path, t.name
  from sde.gdb_items_vw i
  join sde.gdb_itemtypes t
    on i.type = t.uuid
where t.name = 'Feature Class'

forestknutsen1 · Accepted Answer

Okay, I think I got it. The key is to use the class extension from the items xml found in the sde.gdb_items_vw.definition field. In the xml you will find the extclsid or class extension.

Once you know what guid to look for you can make up a sql statement to grab the anno classes.

with fc as
(select i.name, i.physicalname, i.path, i.definition
    from sde.gdb_items_vw i
    join sde.gdb_itemtypes t
      on i.type = t.uuid
   where t.name = 'Feature Class')
select name, physicalname, path
  from fc
where extractvalue(xmltype(fc.definition), '/DEFeatureClassInfo/EXTCLSID') =
       '{24429589-D711-11D2-9F41-00C04F6BC6A5}'