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;
);

It's best to use the standard `varchar2` type for short strings. The [documentation](https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/plsql-data-types.html#GUID-7D0D76A5-D921-4823-91F5-FC9887598795) explicitly warns against using `varchar`, while [char](https://stackoverflow.com/a/42165653/230471) has no practical use and only unexpected side effects. — William Robertson, Nov 20 '22 at 10:31

Jon Heller · Answer 1 · 2022-11-19T20:29:49.263

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;

Thank you, but still getting an error, 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; ERROR at line 1: ORA-00902: invalid datatype — jazy, Nov 19 '22 at 19:31
@jazy See my edit with a fully reproducible test case. You might want to compare my code with your code (including the dependent nested tables) and see exactly what's different. I guessed what TICKET_NT_TYPE and SCHEDULE_NT_TYPE would look like; maybe those types make a difference? — Jon Heller, Nov 19 '22 at 20:30

ORA-02330: datatype specification not allowed

1 Answers1