Oracle SQL nested tables.

Katniss everMean · 21-03-2009 08:57PM #1

Hey, I have been trying to create a table with nested tables. I have fallowed a tutorial on how to do it, and check other places to ensure that my method is correct. Yet it still wont work

Any help would be great.

CREATE OR REPLACE TYPE sector_type AS  OBJECT(
    SID integer,
    AIPS integer
);
CREATE OR REPLACE TYPE state_table_type AS TABLE OF sector_table;

CREATE TABLE run (

    UNO integer,
    RNO VARCHAR(2),
    time intger,
    runLenght integer,
    states state_table_type
)
NESTED TABLE states STORE AS state_table;

Error : ORA-00902: invalid datatype. I assume the error is to do with states state_table_type.

Beano · 22-03-2009 12:53AM

you have the type declared as sector_type but you use sector_table when you create the table of types. shouldnt these be the same?

ronivek · 22-03-2009 08:47PM

LG01? :P

I've added a few lines to your code snippet below; not sure if it's any help.

Angelkat7 wrote: »
Hey, I have been trying to create a table with nested tables. I have fallowed a tutorial on how to do it, and check other places to ensure that my method is correct. Yet it still wont work Any help would be great.
CREATE OR REPLACE TYPE sector_type AS  OBJECT(
    SID integer,
    AIPS integer
);
CREATE OR REPLACE TYPE state_table_type AS TABLE OF sector_table;[U][B]Shouldn't this be a table of sector_type?[/B][/U]

CREATE TABLE run (

    UNO integer,
    RNO VARCHAR(2),
    time intger, [B][U]Should be integer[/U][/B]
    runLenght integer,
    states state_table_type
)
NESTED TABLE states STORE AS state_table;
Error : ORA-00902: invalid datatype. I assume the error is to do with states state_table_type.

Katniss everMean · 22-03-2009 11:52PM

ronivek wrote: »

LG01? :P

I've added a few lines to your code snippet below; not sure if it's any help.

I noticed the sector_type error after I posted. I just feel stupid about the intger

sorry guys.

Oracle SQL nested tables.

Comments