Oracle PL/SQL Nested Table Sorting
Data exists in a PL/SQL nested table in the order it was loaded. There will be gaps in the sequence when elements are deleted from the PL/SQL nested table. The example below shows the way to sort the data in a PL/SQL nested table by ther use of ORDER BY ASC or DESC. Since the data is inserted into the nested table nt_sort by using the ORDER BY 1 NULLS LAST clause after deletion of an element (8), the deletion hasn't created a gap in the sequence. If an element is deleted again, to skip over the gap, EXISTS nt_sort.EXISTS(index) has to be used to display data or use the NEXT or PRIOR methods to skip over the gaps as explained in the nested table iteration .

CREATE OR REPLACE TYPE nt_numtype IS TABLE OF NUMBER;
/

DECLARE
  nt_base nt_numtype := nt_numtype(1, 7, 3, 4, 2, 6, 5, 18);
  nt_sort nt_numtype;
 
  FUNCTION op_nt(i_nt IN nt_numtype) RETURN VARCHAR2 IS
   s_nested_tab VARCHAR2(200);
  BEGIN
   FOR idx IN i_nt.FIRST .. i_nt.LAST LOOP
    IF idx < i_nt.COUNT THEN
     s_nested_tab := s_nested_tab||i_nt(idx)||', ';
    ELSE
     s_nested_tab := s_nested_tab||i_nt(idx);
    END IF;
   END LOOP;
   RETURN s_nested_tab;
  END op_nt;

BEGIN
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'CARDINALITY Of Base Nested Table ['||CARDINALITY(nt_base)||']');
  DBMS_OUTPUT.PUT_LINE('Base Nested Table ['||nt_base.COUNT||'] -> ('||op_nt(nt_base)||')');
 
  SELECT CAST(MULTISET(SELECT * FROM TABLE(nt_base) ORDER BY 1) AS nt_numtype)
  INTO nt_sort FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('Sorted Nested Table ['||nt_base.COUNT||'] -> ('||op_nt(nt_sort)||')');
 
  nt_sort := nt_numtype();
  nt_base.EXTEND;
  nt_base(9) := NULL;
  nt_base.DELETE(8);
 
  SELECT * BULK COLLECT INTO nt_sort
  FROM TABLE(CAST(nt_base AS nt_numtype)) ORDER BY 1 ASC NULLS FIRST;
  DBMS_OUTPUT.PUT_LINE('Sorted Ascending ['||nt_base.COUNT||'] -> ('||op_nt(nt_sort)||')');
 
  SELECT * BULK COLLECT INTO nt_sort
  FROM TABLE(CAST(nt_base AS nt_numtype)) ORDER BY 1 DESC NULLS FIRST;
 
  DBMS_OUTPUT.PUT_LINE('Sorted Descending ['||nt_base.COUNT||'] -> ('||op_nt(nt_sort)||')');
  DBMS_OUTPUT.PUT_LINE(CHR(10)||'CARDINALITY Of Final Nested Table ['||CARDINALITY(nt_base)||']');
END;
/



Back

Oracle registered trademark of Oracle Corporation.
Last Revised On: November 03, 2013

  72834