Re: Accessing one varray element within SQL query

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 12 Jan 2004 21:32:40 GMT
Message-ID: <btv3pm$bi09s$2@ID-82536.news.uni-berlin.de>

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

You might doubt it, but it _is_ possible to do what the OP wanted with my suggested SQL statement. That is, if he can do a 'select * from dual' he can also do a 'select column_value ...'.  

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

Why's that?

Rene

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

Received on Mon Jan 12 2004 - 15:32:40 CST