Unique rows in a nested table

Hello everyone, I just started scripting in PL/SQL and I could use a bit of help with something.

I use Oracle 9, SQL*PLUS 9 and TOAD 8.

My script generate a huge list of values in a nested table. A lot of those values are identical (copies). I would like to keep only the unique in my nested table. Doing the equivalent of a SELECT DISTINCT on my nested table, preferrably outputting the result in a nested table (or at worst, something else).

I've tried a few things, and looked the doc, I couldn't find a non-ugly way to do it. Someone have an idea?

Why don't you post script?

If you were using Oracle10g then you could use the new set operations.

Code:

---

DECLARE
        TYPE nums IS TABLE OF NUMBER;
          num1 nums := nums(1,2,2,3,3,4);
        num2 nums;
BEGIN
        num2 := num1 MULTISET UNION DISTINCT num1;
FOR i IN num2.first..num2.last
LOOP
        dbms_output.put_line(num2(i));
END LOOP;
END;
SQL> /
1
2
3
4

PL/SQL procedure successfully completed.

---

Well I didn't feel it was neccessary to post the script, it wouldn't tell you much. It just generate a list of values in a nested table, and I need to get rid of the duplicates entry in this nested table.

I did some research yesterday. Apparently there is no way to do it simply outside of Oracle 10. So I don't know, I guess I'll take a look at what the other collection type offer me. Due to the huge amount of data, I can't afford an ugly solution that will run slowly.

Code:

---


SQL> get nested_table_2
  1  declare
  2  TYPE NumList IS TABLE OF INTEGER;
  3  nums NumList := Numlist(12,10,8,8,10,12,7,3,7,7);
  4  L_temp int;
  5  begin
  6    dbms_output.put_line('before  sorting ');
  7    FOR I in nums.FIRST ..nums.LAST Loop
  8      dbms_output.put_line(nums(I));
  9    END loop;
 10    --- sort netsted table
 11    FOR M in nums.FIRST .. (nums.LAST - 1) LOOP
 12        FOR N in (M+1) .. nums.LAST LOOP
 13            if nums(M) > nums(N) then
 14              -- swap the values
 15              L_temp := nums(N);
 16              nums(N) := nums(M);
 17              nums(M) := L_temp;
 18            end if;
 19        END LOOP;
 20    END LOOP;
 21    --- using first and last
 22    dbms_output.put_line('after sorting ');
 23    FOR I in nums.FIRST ..nums.LAST Loop
 24      dbms_output.put_line(nums(I));
 25    END loop;
 26    --- delete duplicate
 27    FOR M in nums.FIRST .. (nums.LAST -1) Loop
 28        if nums(M) >= nums(M+1) then
 29          nums(M) := NULL ;
 30        end if;
 31    END loop;
 32    dbms_output.put_line('after deleting duplicates ');
 33    FOR I in nums.FIRST ..nums.LAST Loop
 34      dbms_output.put_line(nums(I));
 35    END loop;
 36* end;
 37  /
before  sorting
12
10
8
8
10
12
7
3
7
7
after sorting
3
7
7
7
8
8
10
10
12
12
after deleting duplicates
3
7
8
10
12

PL/SQL procedure successfully completed.

---

Hope this helps...