How to eliminate duplicate values from a PL/SQL TABLE ~ Oracle Apps Knowledge Sharing

Monday, April 26, 2010

How to eliminate duplicate values from a PL/SQL TABLE

It is easy to select unique values from database table, with the help of select distinct query.

But if you have some data in pl/sql table, then it is very difficult to eliminate duplicate records.

But oracle 10g made it easy to filter out duplicate data from pl/sql table or nested table as well.

This is possible in 10g with a concept called - MULTISET

See following example...

-------

DECLARE
TYPE nested_typ IS TABLE OF VARCHAR2(200);
v_test_type    nested_typ := nested_typ();
v_temp         nested_typ ;
BEGIN
v_test_type.extend(8);
-- read values into array.
v_test_type(1) := 'NEW YORK';
v_test_type(2) := 'SYDNEY';
v_test_type(3) := 'SINGAPORE';
v_test_type(4) := 'PERTH';
v_test_type(5) := 'NEW YORK';
v_test_type(6) := 'NEW YORK';
v_test_type(7) := 'DELHI';
v_test_type(8) := 'PERTH';
v_test_type(9) := 'MADURAI';

dbms_output.put_line('Cities before distinct..'||CHR(10)||'----------------------
-');
-- display values before distinct..
FOR i IN v_test_type.first..v_test_type.last
LOOP
   dbms_output.put_line(v_test_type(i));
END LOOP ;

-- assign all values to v_temp, which are in v_test_type
v_temp := v_test_type ;

-- take ditinct values out of v_temp, v_test_type into v_test_type
v_test_type := v_test_type MULTISET UNION DISTINCT v_temp ;
dbms_output.put_line(CHR(10)||'...'||CHR(10)||'Cities after distinct..'||CHAR(10)
|'-----------------------');

-- display values after distinct..
FOR i IN v_test_type.first..v_test_type.last
LOOP
   dbms_output.put_line(v_test_type(i));
END LOOP ;
END ;
/

OUTPUT
========
Cities BEFORE DISTINCT..

------------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
NEW YORK
NEW YORK
DELHI
PERTH
MADURAI
...
Cities AFTER DISTINCT..
-----------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
DELHI
MADURAI

PL/SQL PROCEDURE successfully completed.
===

In the same way we can use other set operations like union, union all, minus, intersect on pl/sql tables.

Here is another way of doing it..

DECLARE
    presidents_t person_names_t ;
    presidents_t1 person_names_t ;
    CURSOR cur_user IS SELECT user_name FROM fnd_user WHERE ROWNUM < 5;
    CURSOR cur_user1 IS SELECT user_name FROM fnd_user WHERE ROWNUM < 10;

BEGIN
    OPEN cur_user ;
    FETCH cur_user BULK COLLECT INTO presidents_t ;
    CLOSE cur_user ;

    OPEN cur_user1 ;
    FETCH cur_user1 BULK COLLECT INTO presidents_t1 ;
    CLOSE cur_user1 ;


    FOR rec IN (SELECT column_value
                 FROM TABLE (CAST (presidents_t AS person_names_t))
                          UNION
                        SELECT column_value
                  FROM TABLE (CAST (presidents_t1 AS person_names_t))
                 )
        LOOP
            DBMS_OUTPUT.PUT_LINE(rec.column_value);
        END LOOP;
END;
/

Do you think this Article is useful?

3 Responses to “How to eliminate duplicate values from a PL/SQL TABLE”

Anonymous said... April 27, 2012 at 7:13 AM Thank you so much ... Murali
Anonymous said... July 8, 2013 at 9:19 AM can you tell me ..what if it is table of record type..?
Unknown said... April 6, 2017 at 1:30 PM how can we use MULTISET UNOIN DISTINCT with OBJECTS TABLE TYPE ???

Oracle Apps Knowledge Sharing

Monday, April 26, 2010