Re: Sorter of PL/SQL Table Utility

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 28 Apr 1999 13:49:30 GMT
Message-ID: <372910f3.96776757_at_192.86.155.100>

[Quoted] A copy of this was sent to "Adam Tadjkarimi" <adamt_at_hsltd.com> (if that email address didn't require changing) On Tue, 27 Apr 1999 09:10:01 -0500, you wrote:

>Does any kind fellow netter have a good PL/SQL code which can sort elements
>of a PL/SQL table and would like to share it graciously?
>
>adamt_at_hsltd.com
>
>Opinions expressed or questions asked are mine not of my employer.
>

I'll show you how to use SQL to sort the table in 7.x and another, easier way in 8.x.

First the 7.x way. You can use the 'call plsql from sql' feature to make your plsql an in memory SQL table as follows:

SQL> create or replace package demo_sort   2 as
  3 type myArray is table of varchar2(30) index by binary_integer;   4

  4      function get_array_element( p_index in number ) return varchar2;
  5      pragma restrict_references( get_array_element, WNDS, RNDS, WNPS );
  6  
  6      function get_array_count return number;
  7      pragma restrict_references( get_array_count, WNDS, RNDS, WNPS );
  8  
  8      procedure do_demo;
  9  
  9      pragma restrict_references( demo_sort, wnds, rnds, wnps, rnps );

 10 end;
 11 /
Package created.

SQL> create or replace view my_demo_view   2 as
  3 select demo_sort.get_array_element(rownum) data   4 from all_objects
  5 where rownum <= ( select demo_sort.get_array_count from dual )   6 /
View created.

so, the view based on a 'big' table (all_objects) can now be used to index into your plsql table in the demo_sort package by calling the function get_array_element. We provided a function get_array_count to let this view know when to 'stop' calling get_array_element....

SQL> create or replace package body demo_sort   2 as
  3
  3
  3 g_my_plsql_table myArray;
  4 g_my_count number := 0;
  5
  5 function get_array_element( p_index in number ) return varchar2   6 is
  7 begin
  8 return g_my_plsql_table(p_index);   9 end;
 10
 10 function get_array_count return number  11 is
 12 begin
 13 return g_my_count;
 14 end;
 15
 15
 15 procedure do_demo
 16 is
 17 begin

 18      for x in ( select username from all_users where rownum < 10 )
 19      loop
 20          g_my_plsql_table( g_my_count+1 ) := x.username;
 21          g_my_count := g_my_count+1;
 22      end loop;
 23  
 23      for x in ( select data from my_demo_view order by data )
 24      loop
 25          dbms_output.put_line( x.data );
 26      end loop;

 27 end;
 28
 28 end;
 29 /
Package body created.

Now, we can see it at work. the select on the view selects from the plsql table -- we can sort it, we can where on it, we could use it in an IN statement and so on...

SQL> exec demo_sort.do_demo
DBSNMP
SYS
SYSTEM
TRACESVR

WEB$GBRADSHA
WEB$GDEYOUNG
WEB$JBROTHER
WEB$KKISER
WEB$RPPRASAD

PL/SQL procedure successfully completed.

Now, if I had Oracle8, i might use the following, easier way:

SQL> create or replace type myTableType as table of varchar2(25);   2 /

Type created.

SQL>
SQL> declare
  2 l_x myTableType := myTableType();   3
  3 begin

  4      for x in ( select username from all_users where rownum < 10 )
  5      loop
  6          l_x.extend;
  7          l_x(l_x.count) := x.username;
  8      end loop;
  9  
  9      for x in ( select a.column_value data
 10                   from THE ( select cast( l_x as mytableType ) from dual ) a
 11                  order by data )
 12      loop
 13          dbms_output.put_line( x.data );
 14      end loop;

 15
 15 end;
 16 /
DBSNMP
SYS
SYSTEM
TRACESVR

WEB$GBRADSHA
WEB$GDEYOUNG
WEB$JBROTHER
WEB$KKISER
WEB$RPPRASAD

PL/SQL procedure successfully completed.

achieves the same effect but without using the 'call plsql from sql' feature.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Received on Wed Apr 28 1999 - 15:49:30 CEST