ORA-02330: datatype specification not allowed

Question

I tried creating a an object table after successfully creating an object type but i got the error

'datatype specification not allowed'.

Please what am i doing wrong. I am lost at this point.

CREATE OR REPLACE Type Route_t AS Object(
Route_ID CHAR(3),
Route_descr VARCHAR(30),
city VARCHAR(10),
Stop_no NUMBER(3),
Stop_meal VARCHAR(10),
Route_ticket ticket_nt_type,
Route_schedule schedule_nt_type
);


Create Table Route_Tab of Route_t
(primary key (Route_ID),
Nested Table Route_ticket Store As Route_ticket_NTab,
Nested Table Route_Schedule Store As Route_Schedule _NTab;
);

Answer 1

The NESTED TABLE storage clauses must come after the parenthesis. Below is a fully functional example.

--Drop table and types to reset environment.
/*
drop table route_tab;
drop type route_t;
drop type ticket_nt_type;
drop type schedule_nt_type;
*/

--Create nested types.
create or replace type ticket_nt_type as table of varchar2(100);
create or replace type schedule_nt_type is table of varchar2(100);

--Create object.
CREATE OR REPLACE Type Route_t AS Object(
Route_ID CHAR(3),
Route_descr VARCHAR(30),
city VARCHAR(10),
Stop_no NUMBER(3),
Stop_meal VARCHAR(10),
Route_ticket ticket_nt_type,
Route_schedule schedule_nt_type
);

--ORIGINAL version that throws "ORA-02330: datatype specification not allowed".
Create Table Route_Tab of Route_t
(primary key (Route_ID),
Nested Table Route_ticket Store As Route_ticket_NTab,
Nested Table Route_Schedule Store As Route_Schedule _NTab;
);

--NEW version that runs on my system.
Create Table Route_Tab of Route_t
(primary key (Route_ID))
Nested Table Route_ticket Store As Route_ticket_NTab,
Nested Table Route_Schedule Store As Route_Schedule_NTab;