How to remove duplicate values from a nested table of record type`

I have created a nested table of record type which gets populated using a loop . The nested table can get populated with duplicate values. I want to remove those duplicate values.

Below is declared in package specification

TYPE id_rec_type IS RECORD
( id     NUMBER
 ,value   NUMBER);

TYPE id_record IS TABLE OF id_rec_type;

and below code is part of a procedure inside the same package.

DECLARE
    loc_id_records        id_record := id_record();
    p_id_records          id_record := id_record();
BEGIN
    FOR rec IN (SELECT id,value
                FROM   table
                WHERE  active ='Y' )
    LOOP
        loc_id_records.EXTEND;
        loc_id_records(loc_id_records.LAST).id := rec.id;
        loc_id_records(loc_id_records.LAST).value := rec.value;
    END LOOP;

    p_id_records := loc_id_records;
END;

I tried MULTISET UNION DISTINCT but got below error

p_id_records := p_id_records MULTISET UNION DISTINCT loc_id_records;

 PLS-00306: wrong number or types of arguments in call to
         'MULTISET_UNION_DISTINCT'

Is there any way to remove duplicate values from the nested table ?

Use DISTINCT in the query so you do not put duplicate values into the collection in the first place (and you can use BULK COLLECT rather than a cursor loop):

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;
/

Then, for the sample data:

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;

Then you can call it using:

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;
/

Which outputs:

> lang-none
> 1: 100
> 2: 200
>

fiddle

You need add the MAP method to your type to make this objects comparable. Without this Oracle Database not knows how to compare your objects.

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