Error creating nested table with inheritance

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 the applicant file:

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

      

My project involves jobs that applicants can apply for. For this purpose, I create an application_typ like this:

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 these objects which will have 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 
);

      

This all works great. The problem 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;

      

It doesn't work, giving an 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've tried different things. If I change application_typ like this:

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 can create the Job table. Why am I getting an error when using an inherited type?

+3


source to share


1 answer


I tried the following in Oracle 11.2.0.1 and didn't get any errors. I made a small 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;

      

When 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 does not allow you to subtype a type FINAL

. If you don't define any finalizing clause for the base type, the default is FINAL

.

Learn more about Oracle Docs .


If you are real world coding (read the industry) I would suggest using nested tables as column types. You end up spending your entire life trying to invest and untie them. I would suggest that you normalize your schema as much as you need or need, and leave nested tables for operations in your PL / SQL code blocks.

0


source







All Articles