Re: Accessing one varray element within SQL query

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

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

It might but I doubt it. Microsoft rarely does anything to encourage use of a competitors product. Especially one with capabilities that go far beyond those of its own.

Even if it does ... the OP should not be connecting directly to a table.

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