Saturday 24 May 2014


Remove Duplicate values from array

Oracle Database Version 10g and up allows to eliminate duplicate values in array elements using MULTISET operation.
Below is the example
Script
Set serveroutput on ;
DECLARE
   TYPE T_Sampleid IS TABLE OF VARCHAR2 (20);

   arr_sampleid    t_sampleid := T_Sampleid ();
   arr_sampleid2   t_sampleid := T_Sampleid ();
BEGIN
   --Adding 6 samples...
   arr_sampleid.EXTEND;
   arr_sampleid (1) := 'sample G001';
   arr_sampleid.EXTEND;
   arr_sampleid (2) := 'sample G002';
   arr_sampleid.EXTEND;
   arr_sampleid (3) := 'sample G003';
   arr_sampleid.EXTEND;
   arr_sampleid (4) := 'sample G001'; -- duplicate
   arr_sampleid.EXTEND;
   arr_sampleid (5) := 'sample G002'; -- duplicate.
   arr_sampleid.EXTEND;
   arr_sampleid (6) := 'sample G004';

   -- Let's display values
   DBMS_OUTPUT.put_line ('Display all samples...');

   FOR i IN arr_sampleid.FIRST .. arr_sampleid.LAST
   LOOP
      DBMS_OUTPUT.put_line (arr_sampleid (i));
   END LOOP;

   -- same array into array 2
   arr_sampleid2 := arr_sampleid;

   -- Identify distinct and return to main arr_sampleid
   arr_sampleid := arr_sampleid MULTISET UNION DISTINCT arr_sampleid2;

   DBMS_OUTPUT.put_line ('Display DISTINCT samples...');

   FOR i IN arr_sampleid.FIRST .. arr_sampleid.LAST
   LOOP
      DBMS_OUTPUT.put_line (arr_sampleid (i));
   END LOOP;
END;
/
Output
processing sample: G11-A15214-02-TA0001 1 1
Display all samples...
sample G001
sample G002
sample G003
sample G001
sample G002
sample G004
Display DISTINCT samples...
sample G001
sample G002
sample G003
sample G004

As you can see in the output, Second set don't have duplicates.
 
Source: http://oraenablement.wordpress.com/2011/06/21/remove-duplicate-values-from-array/ 

No comments:

Post a Comment