Distinct your data in PL/SQL tables

Question: How do you eliminate duplicate values from a PL/SQL table.

Answer: 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';

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 ;
/
out put
========
Cities before distinct..


------------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
NEW YORK
NEW YORK
DELHI
PERTH
...
Cities after distinct..
-----------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
DELHI

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;
/

Sort your nested table data.

Question: How do I sort data which is in a nested table.?

Answer: You might've enjoyed the power of set operations(MINUS,INTERSECT,UNION) in SQL many times.
For example - To combine to sets of data, to eliminate duplicated data among two sets etc.

How about using similar set operations in PL/SQL or Nested tables. ?
With Oracle 10g we can have Multiset operations on Nested tables.

Have you ever come accross a situation, where you need to sort data which is in a nested table.
Below is the sample code to sort nested table and then display it.
---

declare
-- I used one nested table type, which is already available(seeded) in APPS schema.
-- If required, create one table type as you reuired.

v_arr VARCHAR2_TABLE_200 := VARCHAR2_TABLE_200();

begin

v_arr:= VARCHAR2_TABLE_200 ('NEW YORK','SYDNEY', 'SINGAPORE','PERTH', 'NEW DELHI','',NULL,'LONDON');

            -- just print and see, how data is stored in nested table.
                dbms_output.put_line(chr(10)'Cities before sorting..'chr(10)'-----------------------');

for i in v_arr.first..v_arr.last
loop
         dbms_output.put_line(nvl(v_arr(i),'NULL'));

end loop ;

-- watch out NVL function above... wanted to show 'NULL' word, where ever there is NULL.
select cast(multiset(
select * from table(v_arr)
order by 1 asc NULLS FIRST) as VARCHAR2_TABLE_200)
into v_arr from dual;

-- NULLS FIRST in above statement is to display nulls first and then data. Default is nulls displayed last.
-- see if data is sorted or not...

dbms_output.put_line(chr(10)'Cities after sorting..'chr(10)'-----------------------');

for i in v_arr.first..v_arr.last
loop
          dbms_output.put_line(nvl(v_arr(i),'NULL'));
end loop ;
end ;
/

---- here's the output. -------

Cities before sorting..
-----------------------
NEW YORK
SYDNEY
SINGAPORE
PERTH
NEW DELHI
NULL
NULL
LONDON

Cities after sorting..
-----------------------
NULL
NULL
LONDON
NEW DELHI
NEW YORK
PERTH
SINGAPORE
SYDNEY

PL/SQL procedure successfully completed.
..