Remove duplicates from associative array with multiple columns in PL/SQL

Question

I have seen several great examples of removing duplicates in an Oracle associative array using MULTISET UNION DISTINCT and SET. It works great when there is only a single column. I have an associative array based on a RECORD type which contains 3 columns. Is it possible to use the methods mentioned above?

DECLARE
  TYPE rec_type IS RECORD(
     column1 VARCHAR2(5)
    ,column2 VARCHAR2(5));
  TYPE my_aa IS TABLE OF rec_type;
  p_tbl my_aa := my_aa();
  q_tbl my_aa := my_aa();
BEGIN
  p_tbl.extend(4);
  p_tbl(1).column1 := 'A1';
  p_tbl(1).column2 := 'a';
  --
  p_tbl(2).column1 := 'A1';
  p_tbl(2).column2 := 'b';
  --
  p_tbl(3).column1 := 'A1'; -- Dup 
  p_tbl(3).column2 := 'a'; -- Dup
  --
  p_tbl(4).column1 := 'A1';
  p_tbl(4).column2 := 'c';
  --
  dbms_output.put_line('-- First output contains duplicated');
  --
  FOR a IN p_tbl.first .. p_tbl.last LOOP
    dbms_output.put_line(a || ' = ' || p_tbl(a).column1 || ' / ' || p_tbl(a).column2);
  END LOOP;
  --
  --
  q_tbl := p_tbl MULTISET UNION DISTINCT p_tbl;
  --
  --
  dbms_output.new_line;
  dbms_output.put_line('-- Duplicates have been removad');
  FOR a IN q_tbl.first .. q_tbl.last LOOP
    dbms_output.put_line(a || ' = ' || q_tbl(a).column1|| ' / '||q_tbl(a).column2);
  END LOOP;
END;

Answer 1

It is impossible to do distinction by MULTISET UNION DISTINCT with underlying RECORD type due to doc

The element types of the nested tables must be comparable. Please refer to "Comparison Conditions " for information on the comparability of nonscalar types. https://docs.oracle.com/cd/B12037_01/server.101/b10759/operators006.htm

Records are not comparable. You can use user defined objects with MAP method for this

Two objects of nonscalar type are comparable if they are of the same named type and there is a one-to-one correspondence between their elements. In addition, nested tables of user-defined object types, even if their elements are comparable, must have MAP methods defined on them to be used in equality or IN conditions. https://docs.oracle.com/cd/B12037_01/server.101/b10759/conditions002.htm#i1033286

If you still whant do this with records then try SELECT DISTINCT ... FROM TABLE() approach, but your types must be defined on schema level for server older than oracle 12c.