I ported this query, but I may have goofed up somewhere, so please
check it out and if correct me where I am wrong (I'm posting this here
so others can benefit from th example):
select
types.table_name, types.id_column, attr.attribute_name,
attr.datatype
from
acs_attributes attr,
( select
object_type, table_name, id_column, level as
inherit_level
from
acs_object_types
where
object_type <> 'acs_object'
and
object_type <> 'content_revision'
connect by
prior supertype = object_type
start with
object_type = :content_type) types
where
attr.object_type (+) = types.object_type
order by
types.inherit_level desc
becomes:
select
types.table_name, types.id_column, attr.attribute_name,
attr.datatype
from
acs_attributes attr OUTER JOIN
( select
o2.object_type, o2.table_name, o2.id_column,
tree_level(tree_sortkey)as inherit_level
from
( SELECT *
FROM acs_object_types
WHERE object_type = :content_type
AND object_type <> 'acs_object'
AND object_type <> 'content_revision'
) o1,
acs_object_types o2
where
o2.tree_sortkey <= o1.tree_sortkey
and o1.tree_sortkey like (o2.tree_sortkey || '%')
) types USING (attr.object_type)
where
attr.object_type = types.object_type
order by
types.inherit_level desc