Re: Accessing one varray element within SQL query

From: VC <boston103_at_hotmail.com>
Date: Tue, 13 Jan 2004 02:57:41 GMT
Message-ID: <F8JMb.37654$na.30912@attbi_s04>

Hello Aviv,

"Aviv" <ronensh_at_hotmail.com> wrote in message news:f44c6b66.0401121018.3fccc31f_at_posting.google.com...
> 10x for your comments,
>
> My situation is little bit different-
> 1. My .NET client is actually a server using Oracle Data Provider.
> 2. I need to get pretty big result set and not a single record, so I'd
> rather use sql and not PL/SQL.
>
> My purpose is to 'flatten' the varray data as columns so for next
> table
> create or replace table myTable (
> ID number,
> arraySize number,
> arr myArray_ty
> );
> I perform the query:
>
> select id, arr(1), arr(2), arr(3) from myTable where arraySize=3
>
> and take the result into an OracleDataReader.
>

You can do it like this:

SQL> create table myTable (

  2     ID        number,
  3     arraySize number,
  4     arr       myVArray_ty

  5 );

Table created.

SQL> insert into mytable values(1, 3, myvarray_ty(5,1,7));

1 row created.

SQL> insert into mytable values(2, 3, myvarray_ty(25,11,79));

1 row created.

SQL>
SQL> ... and the query, a little clunky:

SQL> select id,

  2        max(decode(rn, 1, column_value)) val1,
  3        max(decode(rn, 2, column_value)) val2,
  4        max(decode(rn, 3, column_value)) val3

  5 from
  6 (select id, column_value, row_number() over (partition by id order by column_value) rn from mytable, table(arr) where arraysize=3)   7 group by id;

        ID VAL1 VAL2 VAL3 ---------- ---------- ---------- ----------

         1          1          5          7
         2         11         25         79

I am not sure, though, it's a good idea. The data model looks a bit strange...

Rgds.

VC Received on Mon Jan 12 2004 - 20:57:41 CST