create or replace procedure cnav_remove_relation
(
 p_status OUT number,
 p_member_group_id IN number default null,
 p_group_id IN number default null
)
IS
v_ruleid number;
cursor c_ruleid is 
 select rule_id  
   from group_member
   where member_group_id=p_member_group_id
     and group_id=p_group_id;
BEGIN
 if (p_group_id is null or p_member_group_id is null) then
   p_status := 1;
   return;
 end if;
 open c_ruleid;
 loop  --- for each matching ruleid     
    fetch c_ruleid into v_ruleid;
    exit when c_ruleid%NOTFOUND;
    delete from group_member where 
       listfind(to_char(v_ruleid),parent_rule_group,'.')=1;
    commit;
 end loop;   
 p_status:=0; 
END cnav_remove_relation;
/
show errors;