How to retrieve distinct values from nested table in Oracle

Question

I have a problem where in I need to retrieve distinct values out of a nested table collection.

Example code:

FUNCTION get_part_atts
       ( p_gp_id IN dummy_di_parts.di_gp_id%TYPE
       , p_attribute_name   IN dummy_part_attr_def.attribute_name%TYPE
       , p_sel1        IN dummy_di_part_atts.sel1%TYPE DEFAULT NULL
       , p_sel2        IN dummy_di_part_atts.sel2%TYPE DEFAULT NULL
       , p_sel3        IN dummy_di_part_atts.sel3%TYPE DEFAULT NULL )
    RETURN dummy_pkg.part_atts_tabtype
    RESULT_CACHE
    IS

l_dummy_part_seq  dummy_di_parts.dummy_part_seq%TYPE;
l_attribute_id       dummy_part_attr_def.attribute_id%TYPE;
l_default_value      dummy_part_attr_def.default_value%TYPE;

l_return part_atts_tabtype := part_atts_tabtype();

BEGIN

  SELECT p.dummy_part_seq
    INTO l_dummy_part_seq
    FROM dummy_di_parts p
   WHERE p.di_gp_id = p_gp_id
     AND p.di_part_status = 'ACTIVE';

EXCEPTION
WHEN NO_DATA_FOUND
THEN RAISE_APPLICATION_ERROR(-20021,'No active parts found  '||p_gp_id||'.');
WHEN TOO_MANY_ROWS
THEN RAISE_APPLICATION_ERROR(-20022,'More than one active part  '||p_gp_id||'.');
END part_lookup; 

BEGIN

  SELECT pad.attribute_id
       , pad.default_value
    INTO l_attribute_id
       , l_default_value
    FROM dummy_part_attr_def pad
   WHERE pad.attribute_name = p_attribute_name;

EXCEPTION
WHEN NO_DATA_FOUND
THEN RAISE_APPLICATION_ERROR(-20023,p_attribute_name||' is not a valid  attribute name.');
END attribute_def_lookup;


SELECT pa.attribute_value
  BULK COLLECT INTO l_return
  FROM dummy_di_part_atts pa
 WHERE pa.dummy_part_seq = l_dummy_part_seq
   AND pa.attribute_id = l_attribute_id
   AND ( p_sel1 IS NULL OR
         pa.sel1 = p_sel1 )
   AND ( p_sel2 IS NULL OR
         pa.sel2 = p_sel2 )
   AND ( p_sel3 IS NULL OR
         pa.sel3 = p_sel3 );

  RETURN l_return;

END get_part_atts;

Now I need to select Distinct from the collection l_return which is a nested table type

Please help

Answer 1

Try using "multiset union distinct". For example, a function that splits a delimited string into tokens and returns a nested table:

CREATE OR REPLACE function fn_split(i_string in varchar2, i_delimiter in varchar2 default ',', b_dedup_tokens in number default 0)
return sys.dbms_debug_vc2coll
as
  l_tab sys.dbms_debug_vc2coll;
begin
  select regexp_substr(i_string,'[^' || i_delimiter || ']+', 1, level)
  bulk collect into l_tab
  from dual
  connect by regexp_substr(i_string, '[^' || i_delimiter || ']+', 1, level) is not null
  order by level;

  if (b_dedup_tokens > 0) then
    return l_tab multiset union distinct l_tab;
  end if;
  return l_tab;
end;

Testing:

select * from table(fn_split('x,x,y,z', ',', 1));

Output:

COLUMN_VALUE
x
y
z