Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to create a SQL view containing nested tables

GwydionDec 4 2013 — edited Dec 13 2013

Hi everybody,

I have a (hopefully) rather simple question. Assuming two tables MAIN and DETAIL and a 1:N relationship between them (a main object may have multiple details), what is the most straightforward way of creating an SQL view that selects all columns of MAIN and a column that contains all corresponding rows of DETAIL as a nested table? Or, in other words, I want to get all the data of a MAIN "object" including all its "nested objects" in a single row.

I have seen examples which create a TABLE OF DETAIL%ROWTYPE and use the MULTISET operator to query DETAIL as a nested table. However, %ROWTYPE seems to be available in PL/SQL only, and I need an SQL view.

As always, there seem to be many ways to achieve this. Since I have lots of detail tables, I want to reduce amount of glue code (like creating intermediate types, etc.) to the minimum.

Regards,

Pat

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 10 2014
Added on Dec 4 2013
11 comments
1,581 views