Re: PROBLEM WITH CREATING NESTED TABLE

From: Paul Nguyen <pnguyen_at_netexplorer.com>
Date: Mon, 01 Mar 1999 15:43:31 -0600
Message-ID: <36DB0A03.A7788D4A@netexplorer.com>

Hi Kyte and everybody,

I have tried to run this script again and again .. but the result is still the same:

SQLWKS> CREATE OR REPLACE TYPE SPECIFIC_ITEM_TYPE2 AS OBJECT
     2>  (ID NUMBER(10),
     3>   DESCRIPTION VARCHAR2(132)
     4>  )
     5> /

Statement processed.

SQLWKS> CREATE OR REPLACE TYPE ORDER_LINE_ITEM_TYPE2 AS OBJECT
     2>  (ITEM_NO NUMBER(6),
     3>   LINE_COST NUMBER(5,3)
     4>  ,INVENTORY_SPECIFIC_REF REF SPECIFIC_ITEM_TYPE2
     5>  )
     6> /

Statement processed.

SQLWKS> CREATE OR REPLACE TYPE CO_SPECIFIC_ORDER_TYPE2 AS OBJECT
     2>  (COMAPANY_ID VARCHAR2(10)
     3>  ,INVOICE_AMT NUMBER(9,3)
     4>  ,INVOICE_NO VARCHAR2(25)
     5>  ,TRACKING_NO VARCHAR2(25)
     6>  ,STATUS VARCHAR2(5)
     7>  ,DELAYEDMSG_SENT_DATE DATE
     8>  )
     9> /

Statement processed.
SQLWKS> CREATE TYPE CO_ORDER_LIST_TYPE2 AS TABLE OF CO_SPECIFIC_ORDER_TYPE2      2> /
Statement processed.
SQLWKS> CREATE TYPE ITEM_LIST_TYPE2 AS TABLE OF ORDER_LINE_ITEM_TYPE2      2> /
Statement processed.

SQLWKS> CREATE OR REPLACE TYPE ORDER_TYPE2 AS OBJECT
     2>  (ORDER_ID VARCHAR2(30)
     3>  ,ORDER_DATE DATE
     4>  ,ORDERLIST_LINE_ITEM ITEM_LIST_TYPE2
     5>  ,CO_SPECIFIC_ORDER_LIST CO_ORDER_LIST_TYPE2
     6>  )
     7> /

Statement processed.

SQLWKS> CREATE TABLE OBJ_ORDER OF ORDER_TYPE2
     2>      (ORDER_ID NOT NULL)
     3>       NESTED TABLE ORDERLIST_LINE_ITEM STORE AS OBJ_ORDER_NT60
     4>        ,
     5>             NESTED TABLE CO_SPECIFIC_ORDER_LIST STORE AS OBJ_ORDER_NT65
     6>
     7>

CREATE TABLE OBJ_ORDER OF ORDER_TYPE2
*
ORA-02320: failure in creating storage table for nested table column ORDERLIST_LINE_ITEM
ORA-00900: invalid SQL statement

Please send me some help,

Paul P. Nguyen

Thomas Kyte wrote:

> A copy of this was sent to Paul Nguyen <pnguyen_at_netexplorer.com>
> (if that email address didn't require changing)
> On Mon, 01 Mar 1999 14:02:59 -0600, you wrote:
>
> >Hello everybody,
> >
> >Can somebody helps me to fix this create object table statement:
> >CREATE TABLE OBJ_ORDER OF ORDER_TYPE
> > (ORDER_ID NOT NULL)
> > NESTED TABLE ORDERLIST_LINE_ITEM STORE AS OBJ_ORDER_NT60
> > ,
> > NESTED TABLE CO_SPECIFIC_ORDER_LIST STORE AS OBJ_ORDER_NT65
> >;
> >The message I got back was:
> >CREATE TABLE OBJ_ORDER OF ORDER_TYPE
> >*
> >ORA-02320: failure in creating storage table for nested table column
> >ORDERLIST_LINE_ITEM
> >ORA-00903: invalid table name
> >
> >Look like I have some storage problem.. can anyone give me some hints ?
> >
> >
> >Thanks in advance,
> >
> >Paul P. Nguyen
> >
>
> need to see the larger example and would be helpful to have database versions
> and platforms as well. I just ran:
>
> SQL> create or replace type myarray as table of number;
> 2 /
>
> Type created.
>
> SQL>
> SQL> create or replace type order_type as object
> 2 ( order_id int,
> 3 orderlist_line_item myArray,
> 4 co_specific_order_list myArray
> 5 )
> 6 /
>
> Type created.
>
> SQL>
> SQL> CREATE TABLE OBJ_ORDER OF ORDER_TYPE
> 2 (ORDER_ID NOT NULL)
> 3 NESTED TABLE ORDERLIST_LINE_ITEM STORE AS OBJ_ORDER_NT60
> 4 ,
> 5 NESTED TABLE CO_SPECIFIC_ORDER_LIST STORE AS OBJ_ORDER_NT65
> 6 ;
>
> Table created.
>
> so the syntax is OK but need to see your types. give us the whole reproducible
> example....
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Mon Mar 01 1999 - 15:43:31 CST