Create object table using type with supertype attribute

Question

I am trying to create an oracle table of oracle-object type.

Here is how my object structure looks like

CREATE OR REPLACE TYPE PERS_T AS OBJECT 
( 

 empno number(4)
, ename varchar2(10)
, job varchar2(9)
, hiredate DATE
, sal number(7,2)
, comm number(7,2)
, deptno number(2)
)NOT FINAL;

CREATE OR REPLACE TYPE EMP_T FORCE UNDER pers_t (
  mgr pers_t
);

All these are fine, but what when I am trying to create a table of EMP_T type using

CREATE TABLE table_name(emp_type EMP_T);

I am getting error

SQL Error: ORA-30756: cannot create column or table of type that contains a supertype attribute

Is it possible in oracle to create table like this?

Answer 1

You are not permitted to sub-type an IS-A relationship using the UNDER keyword simultaneously with a HAS-A relationship using the same type. This ultimately translates to creating an object that refers to itself and it would lead to infinite recursion. You can instead have a pointer to the object of the same type within your code using REF as follows:

CREATE OR REPLACE TYPE EMP_T UNDER PERS_T (
  mgr REF PERS_T
);

create table table_name(emp_type emp_t);
desc table_name;
Name     Null Type    
-------- ---- ------- 
EMP_TYPE      EMP_T() 

IS-A and HAS-A relationships in Oracle type inheritance

IS-A and HAS-A relationships both enable polymorphism and reusability of code but they define fundamentally different relationships between two types.

IS-A

The UNDER keyword is intended to sub-type relationships using the IS-A inheritance model. For example, employee IS-A person who has an emp_id as defined using:

create type person_type as object(ssn number, address varchar2(100)) NOT FINAL;

-- Employee is a **person** with an emp_id.
create type employee_type under person_type(emp_id number)

The idea is that an object of employee_type is also an object of person_type but with the additional specification of the emp_id. This lets for the inheritance model of polymorphism by enabling code reuse via extension of the person_type supertype. Note that it is always possible to define another extension of the person_type student as follows:

-- Student is a person with a student_id
create type student_type under person_type(student_id number)

HAS-A

As such, Oracle does not force upon us any keywords when attempting to create a composite sub-type. It is not syntactically inappropriate to define a sub-type containing one or more super-types as freely as other native types. For example, it is legal to define a composite sub-type classroom_type by composing it with a teacher of type person, 3 students each of type person as well as a room number of type number such as:

create type classroom_type as object ( teacher person_type, 
                                   student1 person_type, 
                                   student2 person_type, 
                                   student3 person_type, 
                                   room_number number)

The HAS-A relationship exists between two objects when one object belongs-to the other i.e. the former object consists of the latter.

However, this practice can cause unnecessary replication of large objects as you are copying the data from one place to another. To make things more efficient, akin to the concept of passing by reference in programming, the REF modifier lets you pass a pointer to an object for use in various scenarios including composite sub-types. Therefore, the above DDL of the class_type can be rewritten as:

create type class_type as object ( teacher ref person_type, 
                                   student1 ref person_type, 
                                   student2 ref person_type, 
                                   student3 ref person_type, 
                                   room_number number)

and is much more efficient. It is useful to note that changes to the referenced object will automatically be propagated downstream.

---

In the example in the question OP wishes to use both an IS-A relationship as well as a HAS-A relationship simultaneously such as in

An employee "is a" person "has a" manager (who "is a" person)

which is a fair translation of a real-world concept. However, to make it legal without leading to infinite recursion, we modify it as:

An employee "is a" person "has a" manager (who "is a" 'reference to' a person)

Answer 2

I don't think so. According to Oracle's own support database, the following applies to a ora-30756

Error Text, Cause and Action from Message File/s for ORA-30756

Versions 9.2, 10.1, 10.2, 11.1, 11.2, 12.1

Error: ORA-30756 cannot create column or table of type that contains a supertype attribute

Cause: The user tried to create a column or table of an object type that contains a supertype attribute. This is not supported because it leads to infinite recursion in our current storage model. Note that creating a column of a type implies that we create columns corresponding to all subtype attributes as well. Action: Change the type definition to contain a supertype REF attribute instead of the supertype object attribute.

You have created a super type PERS_T so I believe this is your problem. As the article states, you can create a reference attribute instead of an object attribute as a work around as @tbone explained - CREATE TABLE table_name(emp_type REF EMP_T);