Re: Oracle8 - nested tables within nested tables?

From: Andrew Protasov <oracle_at_protasov.kiev.ua>
Date: Sun, 6 Dec 98 10:48:51 +0200
Message-ID: <ABpJaQsK42@protasov.kiev.ua>

Hi,

Show errors definitely explaines that nested table can not be inside of nested table:

SVRMGR> create type DATA_POINT as object(
     2> x       number,
     3> y       number,
     4> z       number
     5> );

Statement processed.
SVRMGR> create type DATA_POINT_list AS TABLE OF DATA_POINT; Statement processed.

SVRMGR> create type SUB_MAP as object (
     2> map_no          number,
     3> data            DATA_POINT_list
     4> );

Statement processed.
SVRMGR> create type SUB_MAP_list as table of SUB_MAP; MGR-00072: Warning: TYPE SUB_MAP_LIST created with compilation errors. SVRMGR> show errors
Errors for TYPE SUB_MAP_LIST:
LINE/COL ERROR

---

1/31     PLS-00534: A Table type may not contain a nested table type or VARRAY.
0/0      PL/SQL: Compilation unit analysis terminated

You should implement submaps as ordinary table with foreign key or ref to map_table.

Andrew Protasov

> 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 Sun Dec 06 1998 - 02:48:51 CST