create or replace procedure cnav_search_by_name
(
 p_group_name_list OUT long,
 p_group_id_list OUT long,
 p_search_text IN varchar2 
)
IS
cursor c_groupname is select  
		  group_name,group_id
		from
		  grouping
	     where
		  upper(group_name) like '%'||upper(p_search_text)||'%' or
          soundex(group_name)=soundex(p_search_text);
v_groupname varchar2(2000);
v_groupid number;
BEGIN
 if (p_search_text is null) then
   p_group_name_list := null;
   p_group_id_list := null;
   return;
 end if;
 p_group_name_list := null;
 p_group_id_list := null;
 open c_groupname;
      loop
         fetch c_groupname into v_groupname,v_groupid;
         exit when c_groupname%NOTFOUND;
         if (p_group_name_list is null) then
            p_group_id_list := to_char(v_groupid);
            p_group_name_list := v_groupname;
          else 
           if (v_groupname is not null) then 
             p_group_id_list := p_group_id_list || ',' || to_char(v_groupid);
             p_group_name_list := p_group_name_list || ',' || replace(v_groupname,',','%44');
           end if;  
         end if; 
      end loop;    
END cnav_search_by_name;
/
show errors;