Re: Querying objects in nested table with Oracle 9i

chandra.patni_at_gmail.com wrote in
news:1126748543.045337.208720_at_g43g2000cwa.googlegroups.com:

> With Oracle 10g, one case use submultiset and query for object type
> equality in where cause. For example:
>
>
> create or replace type oid_collection as table of number(10)
> /
>
> create table indexed_properties
> (
> parent_oid number(10),
> properties oid_collection
> )
> nested table properties store as oid_collection_t;
>
>
> insert into indexed_properties values (10, oid_collection(1, 2, 3, 4,
> 5, 6, 7, 8, 9));
> insert into indexed_properties values (11, oid_collection(1, 2, 3, 4,
> 5));
> insert into indexed_properties values (12, oid_collection(1, 2, 3));
> insert into indexed_properties values (13, oid_collection(3, 1, 2));
> insert into indexed_properties values (14, oid_collection(5, 6, 7));
> commit;
>
>
> SQL> select parent_oid from indexed_properties where properties =
> oid_collection(1, 2, 3);
>
> PARENT_OID
> ----------
> 12
> 13
>
> SQL> select parent_oid from indexed_properties where OID_COLLECTION(1,
> 2, 3) submultiset of PROPERTIES;
> PARENT_OID
> ----------
> 10
> 11
> 12
> 13
>
> Is there a way to execute select statments in Oracle 9i?
>
>

Yes, you just did it.
select parent_oid from indexed_properties where OID_COLLECTION(1,2, 3) submultiset of PROPERTIES; Received on Wed Sep 14 2005 - 21:16:39 CDT