Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Q-Sort for PL/SQL Tables
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;
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;
hope this helps.
chris.
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.