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!

nested tables of user defined types

319523Sep 18 2002
Hello all,
I have philosophical question I am hoping someone can answer for me, "To TYPE, or not to TYPE; that is the question."

I have created several layers of nested tables in the form:
CREATE TYPE xyz AS OBJECT();

CREATE TYPE table_xyz AS TABLE OF xyz;

CREATE TYPE abc AS OBJECT(
nested_table1 table_xyz;
);
..
and so on until I end up creating a main table with all these sub tables nested into it.
But from what I understand oracle stores all of the information in a column of type NESTED TABLE into a single table, rather than one table per row. This means that if I declare a primary key in the nested table it will be against all values in the outer column, not just limited to the values in an individual row of the outer table. Plus it will not let me create foreign keys with in a nested table.

So why should I use the above construct instead of simply creating separate tables, and use primary and foreign keys to relate them and joins to query?

I can see the use for types in small cases (addresses, person, etc..) but I do not see the advantage of nested tables. Could someone shed some light on the subject for me?

Thanks,
Thomas
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 21 2002
Added on Sep 18 2002
2 comments
504 views