在查询中使用DISTINCT，这样你就不会把重复的值放在集合中（你可以使用BULK COLLECT而不是游标循环）：

CREATE PACKAGE package_name AS
  TYPE id_rec_type IS RECORD
  (
    id     NUMBER,
    value  NUMBER
  );

  TYPE id_record IS TABLE OF id_rec_type;

  PROCEDURE procedure_name(
    o_ids OUT id_record
  );
END;
/

CREATE PACKAGE BODY package_name AS
  PROCEDURE procedure_name(
    o_ids OUT id_record
  )
  IS
  BEGIN
    SELECT DISTINCT id, value
    BULK COLLECT INTO o_ids
    FROM   table_name
    WHERE  active ='Y';
  END;
END;
/

然后，对于样本数据：

CREATE TABLE table_name (id, value, active) AS
SELECT 1, 100, 'Y' FROM DUAL UNION ALL
SELECT 1, 100, 'Y' FROM DUAL UNION ALL
SELECT 2, 200, 'Y' FROM DUAL UNION ALL
SELECT 2, 200, 'Y' FROM DUAL;

然后你可以调用它使用：

DECLARE
  v_ids package_name.id_record;
BEGIN
  package_name.procedure_name(v_ids);

  FOR i IN 1 .. v_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_ids(i).id || ': ' || v_ids(i).value);
  END LOOP;
END;
/

其输出：

1: 100
2: 200

fiddle

您需要将MAP方法添加到您的类型中以使此对象具有可比性。没有这个Oracle数据库不知道如何比较您的对象。

create or replace type id_rec_type force as object ( 
  id     NUMBER,
 value   NUMBER,
 map MEMBER FUNCTION id_rec_type_map RETURN number
);
/
CREATE OR REPLACE TYPE BODY id_rec_type is
 map MEMBER FUNCTION id_rec_type_map RETURN number is begin
  return id*1000 + value;
 end id_rec_type_map;
end;
/
DECLARE
  TYPE nested_typ IS TABLE OF id_rec_type;
    loc_id_records        nested_typ := nested_typ();
    p_id_records          nested_typ := nested_typ();
    procedure dump_table(i_comment in varchar2, i_records in nested_typ) is
      i_cur_idx pls_integer;
    begin
      dbms_output.put_line(i_comment || '(' || i_records.count || '): ');
      i_cur_idx  := i_records.first;
      while i_cur_idx is not null 
      loop
        dbms_output.put_line('(' || to_char(i_records(i_cur_idx).id) || ',' || to_char(i_records(i_cur_idx).value) || ')');
        i_cur_idx := i_records.next(i_cur_idx);
      end loop;
    end dump_table;
BEGIN
    FOR rec IN (SELECT id,value
                FROM   (
                select 1 id ,1 value from dual union all
                select 1,2 from dual union all
                select 1,1 from dual union all
                select 1,3 from dual union all
                select 2,1 from dual union all
                select 2,2 from dual union all
                select 2,2 from dual union all
                select 2,3 from dual 
                ) )
    LOOP
        loc_id_records.EXTEND;
        loc_id_records(loc_id_records.LAST) := id_rec_type (rec.id, rec.value);
    END LOOP;

    p_id_records := loc_id_records;
    dump_table('With dups', i_records => p_id_records);
    p_id_records := p_id_records MULTISET UNION DISTINCT p_id_records;
    dump_table('Without dups', i_records => p_id_records);
END;
/

dbms_output：

With dups(8): 
(1,1)
(1,2)
(1,1)
(1,3)
(2,1)
(2,2)
(2,2)
(2,3)
Without dups(6): 
(1,1)
(1,2)
(1,3)
(2,1)
(2,2)
(2,3)

dbfiddle