Re: Accessing one varray element within SQL query

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.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)