Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> select from VARRAY - how to preserve order
Hi there,
this is on Oracle 9.2.0.3 64 Bit on HPUX 11.11
I am trying to un-roll data that is stored in a simple varray.
How do I ensure, the data is returned in the VARRAY order.
This is to be used in various joins with other tables.
The issue is that I don't know that to put into the 'order by' clause for my
select statement.
What I would need is something like 'order by bla, blubb, array-index'.
Note, that in this example the data is returned correctly, but the database
engine is free to re-order the result-set any way it likes, unless i have an
order by clause.
I do not want to add an extra order field into the array structure since that
would be redundant (the data is already physically ordered by the VARRAY
structure).
Thanks Karsten
testcase as follows:
KARSTEN> create type test_array is varray(10) of number;
2 /
KARSTEN> create table karsten_table (id number, the_array test_array);
KARSTEN> insert into karsten_table values (1, test_array(1,2,3,4,5,6,7)); KARSTEN> insert into karsten_table values (2, test_array(1,2,3,4,5,6,7));
KARSTEN> select id, column_value
2 from karsten_table, table (THE_ARRAY);
ID COLUMN_VALUE
---------- ------------
1 1 1 2 1 3 1 4 1 5 1 6 1 7 2 1 2 2 2 3 2 4 2 5 2 6 2 7Received on Tue Jun 10 2003 - 08:05:20 CDT