querying varray

From: <geraldine_2_at_comcast.net>
Date: Mon, 28 Nov 2005 16:10:02 +0000
Message-Id: <112820051610.21731.438B2BDA0004E8E0000054E32200734364CDA10A02070B040E9D0A09@comcast.net>

Hi,
I'm having trouble writing a query to return the record containing a particular element in the varray. In the example below, I would like to search for the id that contains 90 in myvarray. The query should return id=2. How can I do that?

thanks.    

create or replace type myvarray as varray(5) of number;
/

create table mytab (

  id		         number,
  numlist                myvarray

)
/

insert into mytab values (1,myvarray(10,20,30,40,50)); insert into mytab values (2,myvarray(60,70,80,90,100));

SQL> select * from mytab;

       ID NUMLIST

---------- ------------------------------
         1 MYVARRAY(10, 20, 30, 40, 50)
         2 MYVARRAY(60, 70, 80, 90, 100)

geraldine

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Nov 28 2005 - 10:12:46 CST