Error in creating nested table involving inheritence

Question

I have a somewhat complex structure as follows :

create or replace type user_typ as object(
  user_id number(19,0),
  username nvarchar2(40 char)
);

I inherit an applicant_typ from this :

create or replace type applicant_typ under user_typ (
  resume_text nclob
);

My design involves jobs to which applicants can apply. To this end, I create an application_typ as follows :

create or replace TYPE Application_typ AS OBJECT (
  application_id NUMBER,
  candidate applicant_typ,
  time_of_app DATE
);

CREATE TYPE Application_tab IS TABLE OF Application_typ; 

And now I want to create an object type called Job_typ, and a table containing those objects, wherein there will be a nested table for applications :

CREATE OR REPLACE TYPE Job_typ AS OBJECT (
  job_ID NUMBER,
  company_ID NUMBER,
  description NVARCHAR2(1000),
  name NVARCHAR2(200),
  application Application_tab,
  MAP MEMBER FUNCTION job_no RETURN NUMBER,
  MEMBER PROCEDURE no_of_applicants 
);

All of this works fine. The issue is when I try to create a table of type Job_typ :

CREATE TABLE Job_tab OF Job_typ 
NESTED TABLE application STORE AS application_nt;

This doesn't work, giving the error :

SQL Error: ORA-02320: failure in creating storage table for nested table column APPLICATION
ORA-22913: must specify table name for nested table column or attribute
02320. 00000 -  "failure in creating storage table for nested table column %s"
*Cause:    An error occurred while creating the storage table for the
       specified nested table column.

What am I doing wrong?

EDIT : I tried some different things. If I change application_typ as follows :

CREATE OR REPLACE TYPE Application_typ AS OBJECT (
  application_id NUMBER,
  candidate User_Typ, -- NOTE: This attribute is now of type User_typ instead of the inherited type
  time_of_app DATE,
);

 CREATE TYPE Application_tab IS TABLE OF Application_typ; 

Then everything else works, and I am able to create the Job table. Why do I get the error on using the inherited type?

Answer 1

I tried the following in Oracle 11.2.0.1 and didn't get any error. I made a slight change though:

CREATE OR REPLACE TYPE user_typ AS OBJECT
(
    user_id NUMBER (19, 0),
    username NVARCHAR2 (40 CHAR)
) NOT FINAL;                            -- << Notice the NOT FINAL keyword

create or replace type applicant_typ under user_typ (
  resume_text nclob
);

CREATE OR REPLACE TYPE Application_typ AS OBJECT
(
    application_id NUMBER,
    candidate applicant_typ,
    time_of_app DATE
);

CREATE TYPE Application_tab IS TABLE OF Application_typ;

CREATE OR REPLACE TYPE Job_typ AS OBJECT
(
    job_ID NUMBER,
    company_ID NUMBER,
    description NVARCHAR2 (1000),
    name NVARCHAR2 (200),
    application Application_tab,
    MAP MEMBER FUNCTION job_no
        RETURN NUMBER,
    MEMBER PROCEDURE no_of_applicants
);

CREATE TABLE Job_tab OF Job_typ
NESTED TABLE application
    STORE AS application_nt;

While trying to create all your types and keywords, I got the error:

[Error] PLS-00590 (10.1): PLS-00590: attempting to create a subtype UNDER a FINAL type

This is because Oracle doesn't allow creation of a subtype on a FINAL type. If you don't define any finalizing clause for the base type, the default is FINAL.

Read more on Oracle Docs.

---

If you are coding for the real world (read industry), I'd advise against using nested tables as column types. You end up spending your entire life trying to nest and un-nest these. I'd suggest you normalize your schema as much as you can or need and leave nested tables for operations in PL/SQL code blocks.

Answer 2

Your code is completely correct and you just need to add "NOT FINAL" end of your script :

create or replace type user_typ as object(
user_id number(19,0),
username nvarchar2(40 char)
)
NOT FINAL;

Good luck