Oracle8 - nested tables within nested tables?

From: Dr Simon Twigger <simont_at_mcw.edu>
Date: Sat, 05 Dec 1998 19:14:42 -0600
Message-ID: <3669DA82.736F73D8@mcw.edu>

Hi there,

Im getting to grips with Oracle8's user defined objects and I was wondering if it is possible to create object types that have nested tables which themselves contain other object types with other nested tables?

For example,

I have entities as follows:

many MAPS each contain 25 SUB_MAPS (numbered 1-25) and each SUB_MAP contains 50 DATA_POINTS (numbered 1-50).

I could set this up relationally by having a MAP_table with a map_id as primary key, then have a SUBMAP_table with joint PK of map_id and SUB_MAP_id and finally a DATA_POINT_table with joint PKs of map_id, SUB_MAP_id and DATA_POINT_id.

With the object approach, what I was hoping to do was something like the following:

create type DATA_POINT as object(

x	number,
y	number,
z	number

);

create type DATA_POINT_list AS TABLE OF DATA_POINT;

/*
use this DATA_POINT_list type as an attribute (nested table) inside the SUB_MAP type:
*/

create type SUB_MAP as object (

map_no     	number,
data		DATA_POINT_list

);

/*
and then create another type
*/
create type SUB_MAP_list as table of SUB_MAP;

/*
and then use this as an attribute (nested table)inside the MAP type, */
create type MAP as object (

 map_id       	number,
 sub_maps	SUB_MAP_list

);

/*
Then create the appropriate object table and somehow describe where to store the two nested tables... */

create table MAP_table of MAP (

map_id		number,
sub_maps	sub_map_list

)
nested table sub_maps STORE AS map_submap_table, nested table sub_maps.data STORE AS map_submap_data_table
);

Needless to say this isnt working! I only get as far as

create type SUB_MAP_list as table of SUB_MAP;

when I get

Warning: Type created with compilation errors.

My question would be, can I make a nested table within a nested table and if so, how might I go about it and tell the database where to store the data for the various nested tables?

Thanks in advance for any suggestions,

        Simon. Received on Sat Dec 05 1998 - 19:14:42 CST