Re: Accessing one varray element within SQL query

> 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.

First: don't crosspost. Most read all comp.db.oracle.* groups, so no need for it.

This sql statement might get you started:

select column_value from myTable, table(myVArray) where rownum = 1;

hth
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch