| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How to create mutually referencing types ?
A copy of this was sent to Erika Grondzakova <Erika.Grondzakova_at_cern.ch>
(if that email address didn't require changing)
On Mon, 14 Dec 1998 16:48:08 +0100, you wrote:
>Hi,
>
>I would like to ask few questions (as I don't have Oracle 8 to try it by
>myself...:-)
>
>Is it possible to create a table with 2 nested tables ? Something like
>this ?
>
yes it is.
SQL> create or replace type countries_nt_type as table of country_type
2 /
Type created.
SQL> create or replace type cities_nt_type as table of city_type
2 /
Type created.
SQL>
SQL> create table state ( countries countries_nt_type,
2 capitals cities_nt_type )3 nested table countries store as countries_nt 4 nested table capitals store as capitals_nt; Table created.
>create type countries_NT as table of Country_type;
>create type capitals_NT as table of City_type;
>
>create table state
>( countries Country_type,
> capitals City_type)
>NESTED TABLE countries STORE AS countries_NT,
>NESTED TABLE capitals STORE AS capitals_NT;
>
>If so, I'm curious how could you insert values if you have a table with
>2 nested tables - columns which are muttually referencing...
>
>insert into state values
>( countries_NT(Country_type('name_of_state','code',???,10,10),
> capitals_NT (City_type('name_of_city',???,5));
>
>What you should put instead of ??? in the insert statement?
>
well, you would have to first create some object instances of Country_Type and City_Type :) . Here you have not created any OBJECTS of these types. You have columns in a table that are of this object type but you have NOT create any object instances to which you could 'ref'.
You see, object refs are only assigned to persistent OBJECT instances. In this example, we have country_type and city_type objects but they are not standalone object instances -- they cannot be "ref'ed". You must create a table of an object type to get a ref, for example:
SQL> create table t1 of city_Type;
Table created.
As opposed to, for example:
SQL> create table t2 ( x city_type );
Table created.
the first create, creates an OBJECT TABLE that can have refs. The second create just creates a table -- no refs allowed, its *not* an object. Only objects in object tables have refs.
So, to answer your question, what would you put instead of ??? here -- I would put NULL since there are no objects to ref and if and when an object of type city or country came into existence -- I would put that ref in the. The rows int he nested tables have no refs.
>Thank you,
>
>Best regards,
>
>Erika
>
>
>Thomas Kyte wrote:
>>
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Dec 14 1998 - 12:28:12 CST
 |
 |