Querying objects in nested table with Oracle 9i

From: <chandra.patni_at_gmail.com>
Date: 14 Sep 2005 18:42:23 -0700
Message-ID: <1126748543.045337.208720@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? Received on Wed Sep 14 2005 - 20:42:23 CDT