Problem: Is there any built-in function in Oracle for selecting distinct values from an associative array.
Answer: You can use SET operator but take into account that the set operator doesn't work on nested tables of PL/SQL records. You can use SET on nested tables of single values.
Look the following example that shows how to get DISTINCT value form an associative array.
SQL>
DECLARE
TYPE num_list IS TABLE OF NUMBER;
id_list num_list := num_list();
id_list_2 num_list := num_list();
BEGIN
FOR i IN (SELECT 1 AS value
FROM all_tables
WHERE ROWNUM < 10
)
LOOP
id_list.EXTEND;
id_list(id_list.LAST) := i.value;
END LOOP;
FOR o IN id_list.FIRST .. id_list.LAST
LOOP
dbms_output.put_line('Org: '||id_list(o));
END LOOP;
id_list_2 := set( id_list ) ;
FOR d IN id_list_2.FIRST .. id_list_2.LAST
LOOP
dbms_output.put_line('New: '||id_list_2(d));
END LOOP;
END;
/
--Output
Org: 1
Org: 1
Org: 1
Org: 1
Org: 1
Org: 1
Org: 1
Org: 1
Org: 1
New: 1
