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?
source to share
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.
source to share