oracle8.1.6 select * from ( select object_id as subsite_id from site_nodes n where (select package_key from apm_packages p where p.package_id = n.object_id) = 'acs-subsite' connect by node_id = prior parent_id start with node_id = :node_id order by level ) where rownum = 1 select object_id as subsite_id, acs_object.name(object_id) as subsite_name from site_nodes n where (select package_key from apm_packages p where p.package_id = n.object_id) = 'acs-subsite' connect by node_id = prior parent_id start with node_id = :node_id order by level