Monday, December 9, 2013

How to eliminate duplicates from PLSQL table?

In Oracle 10g it made easy to filter out duplicate data from pl/sql table or nested table. This is possible in 10g by using "MULTISET" key word.

Following is the example which eliminates duplicate values from the PLSQL table...

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(9);
-- 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..');
-- 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('Cities after distinct..');

-- 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.

No comments:

Post a Comment