This article demonstrates a method used to sort an Oracle nested table collection from within PL/SQL .

The example that follows was built using the Oracle Developer Days Environment and used the following versions:

Oracle Linux running via Oracle Virtual Box
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0
Oracle SQL Developer 3.2.20.10
The user developing and running the code is logged in as the HR user.

The first step is to create a couple of schema level objects.

CREATE TYPE fruit_object AS OBJECT
(
   ID         NUMBER(10),
   FRUIT_NAME VARCHAR2(30)
);
/

CREATE TYPE fruit_table AS TABLE OF fruit_object;
/

The first type to be created is an object which contains the necessary attributes, which in this example is an identifier and a fruit name. Next a nested table is created based upon this object.

The objects need to be created at the database level and not within the PL/SQL code so that the SQL engine can “see” them. If anyone has a method which allows these objects to reside within PL/SQL please let me know via the comments.

With these structures in place, the code to populate and sort the collection is shown below. In order to keep the amount of code to a minimum, I have used a PL/SQL anonymous block.

DECLARE

 lnt_not_sorted  fruit_table := fruit_table();
 lnt_sorted      fruit_table := fruit_table();

BEGIN

 lnt_not_sorted.extend(3);

 lnt_not_sorted(1) := fruit_object(2, 'Banana');

 lnt_not_sorted(2) := fruit_object(99, 'Pineapple');

 lnt_not_sorted(3) := fruit_object(1, 'Apple');

 dbms_output.put_line('Unsorted...');

 FOR i IN 1 .. lnt_not_sorted.COUNT()
 LOOP

  dbms_output.put_line(lnt_not_sorted(i).id || ' ' || lnt_not_sorted(i).fruit_name );

 END LOOP;

 SELECT CAST(MULTISET(SELECT *
                        FROM TABLE(lnt_not_sorted)
                       ORDER BY 1 ) AS fruit_table )
 INTO lnt_sorted
 FROM dual;

 dbms_output.put_line('Sorted...');

 FOR i IN 1 .. lnt_sorted.COUNT()
 LOOP

  dbms_output.put_line(lnt_sorted(i).id || ' ' || lnt_sorted(i).fruit_name );

 END LOOP;

END;

In the declaration section (lines 3 & 4), I declare two variables of the nested table type that was created earlier. As the names reveal, one will be used for the initial unsorted records and another will hold the sorted records. Nested table collections need to be initialised before use and this has done here in the declaration section.

Moving to the executable part of the block, lines 8 – 14 makes space within the collection and then populates it with three rows. Each row of the collection, containing an id and fruit name is populated using the default constructor for the object. The id value will determine where each row should appear once the collection has been sorted.

Lines 18 – 23 output the collection showing its current unsorted order.

Lines 25 – 29, show the most relevant part of the code because this is where the collection is sorted. The SQL used may appear strange the first time you encounter it.

Starting from the inner select statement, The TABLE() operator converts the nested table collection into a row source that allows it to be used in a select statement, note the order by is on the first (id) column. Next the MULTISET operator is used, which tells Oracle to put the multiple rows returned by a select into a single collection object. Finally using the CAST function, Oracle is explicitly told what datatype to convert this to.

With the sorted collection now populated, the contents are displayed to prove they have been sorted.

When run the output of this anonymous block is:

Summary

In this article I have demonstrated one method that allows the sorting of a Nested Table collection.

Acknowledgements:

oracle-developer.net For the explanation of the Table Operator

The CASTMultiset explanation is based upon Tony Andrews superb answer to the following question.