Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Q-Sort for PL/SQL Tables

Re: Q-Sort for PL/SQL Tables

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 02 Feb 1999 23:42:11 GMT
Message-ID: <36b783e1.5695900@inet16.us.oracle.com>


On Tue, 02 Feb 1999 16:07:58 GMT, Charles Arcudi <carcudi_at_eli.net> wrote:

>Has anybody written a q-sort for PL/SQL tables? Odd that Oracle doesn't
>incorporate some kind of sort routine for these things.

This is not a q-sort but gets the job done. Requires Oracle8.

SQL> create or replace type myTableType as table of number;   2 /

Type created.

SQL>
SQL> create or replace
  2 function re_order( l_unordered myTableType )   3 return myTableType as
  4 l_ordered myTableType;
  5 begin
  6 select cast( multiset(

  7           select *
  8             from THE ( select cast( l_unordered as myTableType )
  9                          from dual ) a
 10            order by column_value ) as myTableType )
 11      into l_ordered
 12      from dual;

 13 return l_ordered;
 14 end re_order;
 15 /

Function created.

SQL>
SQL> declare

  2    l_unordered      myTableType := myTableType();
  3    l_ordered        myTableType := myTableType();
  4 begin
  5    l_unordered.extend;
  6    l_unordered(1) := 4;
  7    l_unordered.extend;
  8    l_unordered(2) := 1;
  9    l_unordered.extend;
 10    l_unordered(3) := 20;
 11    l_unordered.extend;
 12    l_unordered(4) := -2;

 13
 13 l_ordered := re_order( l_unordered );  14
 14 for i in 1 .. l_ordered.count loop  15 dbms_output.put_line( l_ordered(i) );  16 end loop;
 17 end;
 18 /
-2
1
4
20

hope this helps.

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.



Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Feb 02 1999 - 17:42:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US