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

• 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. Received on Mon Jan 12 2004 - 09:06:34 CST