Accessing one varray element within SQL query

Giganews Newsgroups
Subject: Accessing one varray element within SQL query
Posted by:  ronen…@hotmail.com
Date: 12 Jan 2004

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.

Replies