Oracle get distict values from array of type number

Question

I have an array type defined below -

TYPE INPUT_ARRAY_NUM IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

I have defined a variable of the above type as -

temp INPUT_ARRAY_NUM;

Populated them with below values -

temp(0) := 1;
temp(1) := 1;
temp(2) := 3;

How can I get distinct values (1, 3) ?

Answer 1

You could use a NESTED TABLE and use MULTISET operation.

The DISTINCT keyword in the MULTISET operation removes the duplicates from the set.

For example,

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2    TYPE t_tab IS TABLE OF NUMBER;
  3    l_tab1 t_tab := t_tab(1,1,3,3,5,5);
  4  BEGIN
  5    l_tab1 := l_tab1 MULTISET INTERSECT DISTINCT l_tab1;
  6    FOR i IN l_tab1.first .. l_tab1.last
  7    LOOP
  8      DBMS_OUTPUT.put_line('Distinct values are '||l_tab1(i));
  9    END LOOP;
 10  END;
 11  /
Distinct values are 1
Distinct values are 3
Distinct values are 5

PL/SQL procedure successfully completed.