Oracle SQL nested tables.

Katniss everMean

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

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

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

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.