Forum .LRN Q&A: Community name from a package_id

I've got the package_id and I want to retrieve the corresponding community name for it.

The packages form a hierarchy and it looks like I need to find a mother package_id before I can join against the dotlrn_communities view.

Can someone point me at a query or proc that pays attention to this hierarchy?

This is in site-wide search, so outside the scope of a package - hence I am looking for a way of doing it in SQL.

This PL/SQL function is easier to use:

create or replace function search_get_community_name(
        v_package_id in integer)
    return varchar2
    is
    v_instance_name     apm_packages.instance_name%TYPE;
begin
select instance_name
    into v_instance_name
  from apm_packages apm, (select  object_id
  from site_nodes
  connect by prior parent_id = node_id start with object_id = v_package_id) sn
where apm.package_id = sn.object_id
and apm.package_key = 'dotlrn'
and rownum=1;
  return v_instance_name;
end;
/
show errors

I think the following function would be more general.

create or replace function search_get_community_name(
        v_package_id in integer)
    return varchar2
    is
    v_community_name    apm_packages.instance_name%TYPE;
begin
select pretty_name into v_community_name
from dotlrn_communities_all dca,
(select  object_id
  from site_nodes
  connect by prior parent_id = node_id start with object_id = v_package_id) sn
where dca.package_id = sn.object_id;
  return v_community_name;
end;
/
show errors