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

Also, I can't make changes to this function to include Distinct in select quiery since it is being called from various other programs and applications. I am looking for a solution where I can get distinct out of the collection that is returned — Tina, Jul 16 '15 at 16:22

score 1 · Answer 1 · answered Jul 16 '15 at 16:35

1

In Oracle 12C you can do this:

select distinct attribute_value
from table (get_part_atts(...))

Prior to 12C you could do similar, but only if the type was defined in the database using CREATE TYPE rather than declared in a package specification.

answered Jul 16 '15 at 16:35

Tony Andrews

129,880
21
220
259

Hi Tony, what about SET operator for collections."The SET operator first converts a nested table into a set, removes duplicate elements from the set, and returns the set as a nested table." – Tina Jul 21 '15 at 21:27

score 0 · Accepted Answer · answered Jul 16 '15 at 16:42

0

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

answered Jul 16 '15 at 16:42

tbone

15,107
3
33
40

Thanks so much tbone..Multiset union distinct seems to be working. I still need to test it. Will get back if I face any issues. Thanks so much for your help. – Tina Jul 17 '15 at 21:52
Hi tbone, what about SET operator for collections."The SET operator first converts a nested table into a set, removes duplicate elements from the set, and returns the set as a nested table." – Tina Jul 21 '15 at 21:26
@Tina set operators in Oracle work on, well, sets of data (think Venn diagrams or "bubble graphs"). Union, intersect, minus, etc. This is what you want, but when working with nested tables in pl/sql, you'll use "multiset union" instead of "union" as with querying a regular table. The "distinct" will dedup the results. For more, see [here](http://docs.oracle.com/cd/B28359_01/server.111/b28286/operators006.htm#SQLRF0032) – tbone Jul 22 '15 at 13:22

How to retrieve distinct values from nested table in Oracle

2 Answers2