create or replace procedure cnav_group_for_psuedo_group
(
 p_group_id_list OUT long,
 p_psuedo_group_id_list IN long,
 p_owner_id IN varchar2 default null, 
 p_viewer_id IN varchar2 default null
)
IS
idValue varchar2(2000);
restStr long; 
v_command long;
v_rows number;
v_cursor number;
countit number;
BEGIN
 if (p_psuedo_group_id_list is null or p_owner_id is null) then
   p_group_id_list := null;
   return;
 end if;
 if (p_psuedo_group_id_list is not null) then  
   v_cursor := DBMS_SQL.OPEN_CURSOR;
   v_command := 'select group_id from grouping ';
   v_command := v_command || ' where owner_entity_id=' || p_owner_id;
   if (p_viewer_id is not null) then
     v_command := v_command || ' and viewer_entity_id=' || p_viewer_id;
   end if;  
   v_command := v_command || ' and parent_group_id in (';
          idValue:=listfirst(p_psuedo_group_id_list,',');
          reststr:=listrest(p_psuedo_group_id_list,',');
          while (idValue is not null) loop
            v_command := v_command ||'''' || idValue || '''';
            idValue:=listfirst(reststr,',');
            reststr:=listrest(reststr,','); 
            if (idValue is not null) then
              v_command := v_command ||',';
            end if;        
          end loop;
   v_command := v_command || ')';
   DBMS_SQL.PARSE(v_cursor,v_command,DBMS_SQL.V7);  
   DBMS_SQL.DEFINE_COLUMN(v_cursor,1,idValue,2000);  
   v_rows := DBMS_SQL.EXECUTE(v_cursor);
   countit:=0;
   p_group_id_list:=null;
   loop 
     if (DBMS_SQL.FETCH_ROWS(v_cursor)=0) then
       EXIT;
     end if;
     countit := countit +1;  
     if (countit!=1) then
       p_group_id_list := p_group_id_list || ',';
     end if; 
     DBMS_SQL.COLUMN_VALUE(v_cursor,1,idValue);
     p_group_id_list := p_group_id_list || idValue;
   end loop;
   DBMS_SQL.CLOSE_CURSOR(v_cursor);
 end if;  
END cnav_group_for_psuedo_group;
/
show errors;