Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02320/ 00904 nested table error (SQLPLUS Oracle database 11g express edition 11.2.0.2.0 64bit)
ORA-02320/ 00904 nested table error [message #677810] Sun, 13 October 2019 00:25 Go to next message
bo
Messages: 5
Registered: October 2019
Junior Member
Receiving a 'failure in creating storage table for nested table column ANNUALCONFERENCES' and 'invalid identifier' error. Unable to locate the invalid identifier, the error occurs after trying to implement the conference table, everything else is created fine. Help would be greatly appreciated.
Re: ORA-02320/ 00904 nested table error [message #677811 is a reply to message #677810] Sun, 13 October 2019 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I bet the second error "invalid identifier" comes from the first one "failure in creating storage table for nested table column ANNUALCONFERENCES" which comes from a problem creating this, the original Oracle error tells you what it is.
We can't say more with what you posted.

[Updated on: Sun, 13 October 2019 00:48]

Report message to a moderator

Re: ORA-02320/ 00904 nested table error [message #677813 is a reply to message #677811] Sun, 13 October 2019 12:51 Go to previous messageGo to next message
bo
Messages: 5
Registered: October 2019
Junior Member
sorry, added my code as a file but I guess it is not visible to you. This is my code
create type paper_t as object(
  author varchar2(4),
  title varchar2(9),
  first int,
  last int
);
create type paper_nt as table of paper_t;

create type location_t as object(
  city varchar2(6),
  country varchar(6)
);
create type location_nt as table of location_t;

create type AC_t as object(
  year int,
  location location_nt,
  papers paper_nt
);
create type AC_nt as table of AC_t;

create table conference(
  name varchar2(4),
  AnnualConferences AC_nt)
  nested table AnnualConferences store as AC_tb(
  nested table location store as location_tb(
  nested table papers store as paper_tb
  )
);
Re: ORA-02320/ 00904 nested table error [message #677814 is a reply to message #677813] Sun, 13 October 2019 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

"location" and "papers" are at the same level, "papers" is not a nested table of "location":
SQL> create type paper_t as object(
  2    author varchar2(4),
  3    title varchar2(9),
  4    first int,
  5    last int
  6  );
  7  /

Type created.

SQL> create type paper_nt as table of paper_t;
  2  /

Type created.

SQL> create type location_t as object(
  2    city varchar2(6),
  3    country varchar(6)
  4  );
  5  /

Type created.

SQL> create type location_nt as table of location_t;
  2  /

Type created.

SQL> create type AC_t as object(
  2    year int,
  3    location location_nt,
  4    papers paper_nt
  5  );
  6  /

Type created.

SQL> create type AC_nt as table of AC_t;
  2  /

Type created.

SQL> create table conference(
  2    name varchar2(4),
  3    AnnualConferences AC_nt)
  4    nested table AnnualConferences store as AC_tb(
  5    nested table location store as location_tb
  6    nested table papers store as paper_tb
  7    )
  8  /

Table created.

[Updated on: Sun, 13 October 2019 14:44]

Report message to a moderator

Re: ORA-02320/ 00904 nested table error [message #677815 is a reply to message #677814] Sun, 13 October 2019 16:12 Go to previous message
bo
Messages: 5
Registered: October 2019
Junior Member
A damn thank you. This is my first time working with sqlplus and nested relations so I'm kinda dumb. This is greatly appreciated!
Previous Topic: Error message: ORA-08103: object no longer exists.
Next Topic: ORA-02315: Incorrect number of arguments for default constructor
Goto Forum:
  


Current Time: Thu Mar 28 17:02:32 CDT 2024