The standard TOracleDataSet functionality for collections is based on a master/detail relation. The table of the master dataset contains one or more collection columns or attributes, that do not necessarily have to be included in the query (for efficiency reasons they can be omitted). The collection can be displayed in a detail dataset that contains a query for a collection column or attribute of the current master record. Lets assume a ROOMS table that contains a RESERVATIONS nested table column. The detail dataset query will be something like this:
select * from table(select m.reservations
from rooms m
where m.room_number = :room_number)The key query is the single record subselect that returns the collection:
select m.reservations
from rooms m
where m.room_number = :room_number
This is the query that returns a single collection column for a single record. If you want to view a collection in a dataset, you must create such a query at design-time or at run-time. It doesn't have to be a master/detail setup either. Let's assume you have presented the user with a selection of rooms, and the user wants to view the reservations for room '13b'. All you need to do is present the result set of the following query:
select * from table(select m.reservations
from rooms m
where m.room_number = '13b')Or, to make it updateable:
select t.*, rowid from table(select m.reservations
from rooms m
where m.room_number = '13b') tAs you can see, there is no master/detail setup necessary. All that is required is that you know the query that returns the collection you want to view or edit.
I hope this helps.
