Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> select from VARRAY - how to preserve order

select from VARRAY - how to preserve order

From: Karsten Schmidt <groups_at_karsten-schmidt.com>
Date: 10 Jun 2003 06:05:20 -0700
Message-ID: <c6711ac4.0306100505.58c695ff@posting.google.com>


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            7
Received on Tue Jun 10 2003 - 08:05:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US