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

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);