Re: creating a table with an object

On Jun 14, 11:16 am, "Micha" <p..._at_gmx.li> wrote:
> first i have create a type:
> TYPE VARCHAR2_TABLE AS TABLE OF VARCHAR2(4000)
>
> then i make an object containing this:
> CREATE OR REPLACE TYPE TYPE_EMAIL_OBJECT AS OBJECT (
> HOSTNAME VARCHAR2_TABLE,
> PORT NUMBER,
> SENDER_EMAIL_ADDRESS VARCHAR2(4000),
> EMAIL_TEXT VARCHAR2_TABLE)
>
> this works.
>
> Now i want to make a table containing this object:
> CREATE TABLE EMAIL_test
> (send VARCHAR2(1),
>
> email TYPE_EMAIL_OBJECT)
>
> at this point i will get an error:
>
> ORA-22913: must specify table name for nested table column or attribute
>
> What must i do to make it working?
>
> Sorry for the bad english and the formatting.
>
> Greetings
>
> Micha

How about reading the error description in the docs? You must specify nested table storage attributes for both nested table attributes you have in the object. At least the table names must be specified, like this:

create table email_test
( send varchar2(1),
  email type_email_object
)
NESTED TABLE email.hostname STORE AS nt$email_test#hostname NESTED TABLE email.email_text STORE AS nt$email_test#email_text /

It is also advisable to index NESTED_TABLE_ID columns of both nested tables to prevent table locks and full scans of nested tables:

create index fk_nt$email_test#hostname on  nt$email_test#hostname(nested_table_id); create index fk_nt$email_test#email_text on  nt$email_test#email_text(nested_table_id);

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Jun 14 2007 - 04:01:57 CDT