create or replace procedure cnav_id_list_for_name_list
(
 p_group_id_list OUT long,
 p_group_name_list IN long
)
IS
nameValue varchar2(2000);
restStr long; 
v_command long;
v_rows number;
v_cursor number;
countit number;
BEGIN
 if (p_group_name_list is null) then
   p_group_id_list := null;
   return;
 end if;
 if (p_group_name_list is not null) then  
   v_cursor := DBMS_SQL.OPEN_CURSOR;
   v_command := 'select group_id from grouping ';
   v_command := v_command || ' where group_name in (';
          nameValue:=listfirst(p_group_name_list,',');
          reststr:=listrest(p_group_name_list,',');
          while (nameValue is not null) loop
            v_command := v_command ||'''' || nameValue || '''';
            nameValue:=listfirst(reststr,',');
            reststr:=listrest(reststr,','); 
            if (nameValue 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,nameValue,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,nameValue);
     p_group_id_list := p_group_id_list || nameValue;
   end loop;
   DBMS_SQL.CLOSE_CURSOR(v_cursor);
 end if;  
END cnav_id_list_for_name_list;
/
show errors;