Re: Sorting an array

In article <8ertuh$rr4$1_at_nnrp1.deja.com>,   Sean <dolans_at_my-deja.com> wrote:
> 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
>

select * from user_types;

drop type myTableType;

Yes, you can drop it if you don't need it anymore.

> 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.
>

--
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.