Re: Accessing one varray element within SQL query

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.

TIA. Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1073921956.539005_at_yasure>...
> Aviv wrote:
>
> > Hello,
> >
> > Is there a simple way to access single element of varray from within
> > SQL statement?
> > I'm using a .NET client, that does not support objects/collections,
> > and I want to get, lets say, the first element of varray column:
> >
> > -- Declare varray type
> > SQL> create or replace type myVArray_ty as varray(10) of number(10);
> >
> > -- Create table using this new type
> > SQL> create table myTable( myVArray myVArray_ty);
> >
> > SQL> insert into mytable values( myVArray_ty(1,2,3,4,5) );
> >
> > SQL> select myVArray from myTable;
> >
> > MYVARRAY
> > -----------------------------------------------------------------------
> > MYVARRAY_TY(1, 2, 3, 4, 5)
> >
> > -- Try to select the first element of the varray
> > -- I would expect to get the number 1
> > SQL> select myVArray(1) from myTable
> > *
> > ERROR at line 1:
> > ORA-00904: "MYVARRAY": invalid identifier
> >
> > Is there a way to do this with member function?
> > The only way I managed to do so is by define a function that get
> > myVArray_ty and index as parameters, and it's a beet awkward way to do
> > this.
> >
> > TIA.
> > Ronen S.
>
> Please do not post the same message to multiple groups.
>
> Your .NET client should not be hitting directly against tables even
> without object/collection data types.
>
> What you should write is a stored procedure, or a procedure within a
> package, to accept input from your .NET client and return the result
> set via a REF CURSOR.
>
> It is far more efficient and it solves the problem.

10x for your comments,

My situation is litle bit different-
1. My .NET client is actually a server using Oracle Data Provider. 2. I need to get a prety big result set and not from a single record, so I'd rather use sql and not PL/SQL.

My perpuse is to 'flaten' the varray data as columns so for the 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.

TIA.
  Ronen S. Received on Mon Jan 12 2004 - 12:18:52 CST