Re: Sorting an array

Thank you. One question though. Once I have created this "type", where do I find it in the Oracle database? Can I remove it if I don't need it anymore and if so, how?

Thanks Again,
Sean

In article <8ernbr$klc$1_at_nnrp1.deja.com>,   Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> In article <8eq53e$ub9$1_at_nnrp1.deja.com>,
> Sean <dolans_at_my-deja.com> wrote:
> > I apologize for the "generalness" of the question, but I am
 wondering
> > the best approach to sorting an array? Oracle 8i, within a stored
> > procedure, an array (PL/SQL table) is built. There is no order to
 the
> > array but I would like to "sort" it. Is there documentation for Pro
> > C++ that explains this or is there a better way to accomplish it?
> >
> > Thanks,
> > Sean
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> If, instead of using a PLSQL "index by binary_integer" table type --
> you create this tabletype at the SQL level (so its an object in the
> data dictionary) we can use SQL on it. Consider:
>
> ops$tkyte_at_8i> create or replace type myTableType as table of number;
> 2 /
>
> Type created.
>
> ops$tkyte_at_8i>
> ops$tkyte_at_8i>
> ops$tkyte_at_8i> create or replace procedure do_something
> 2 as
> 3 l_data myTableType := myTabletype();
> 4 begin
> 5
> 6 for i in 1 .. 10 loop
> 7 l_data.extend();
> 8 l_data(l_data.count) := 10 - i;
> 9 end loop;
> 10
> 11 dbms_output.put_line( 'Unsorted....' );
> 12 for j in 1 .. l_data.count loop
> 13 dbms_output.put_line( l_data(j) );
> 14 end loop;
> 15
> 16 dbms_output.put_line( 'Sorted....' );
> 17 for x in ( select *
> 18 from TABLE ( cast( l_data as myTableType ) )
> 19 order by column_value )
> 20 loop
> 21 dbms_output.put_line( x.column_value );
> 22 end loop;
> 23
> 24
> 25 dbms_output.put_line( 'Sorted.... another syntax...' );
> 26 for x in ( select *
> 27 from THE ( select cast( l_data as mytableType )
> from dual ) a
> 28 order by column_value )
> 29 loop
> 30 dbms_output.put_line( x.column_value );
> 31 end loop;
> 32
> 33 end;
> 34 /
>
> Procedure created.
>
> ops$tkyte_at_8i>
> ops$tkyte_at_8i> exec do_something
> Unsorted....
> 9
> 8
> 7
> 6
> 5
> 4
> 3
> 2
> 1
> 0
> Sorted....
> 0
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> Sorted.... another syntax...
> 0
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
>
> PL/SQL procedure successfully completed.
>
> So, you can in effect "select * from PLSQL-VARIABLE" and sort it, use
> it in a join, use it in a subquery and so on.
>
> --
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries
> http://osi.oracle.com/~tkyte/index.html
> --
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu May 04 2000 - 00:00:00 CDT