Slide 12.4: Creating cyclic object types

Creating Cyclic Object Types

Incomplete types can be created for mutually self-referencing (cyclic) object types. Its type specification does not have a list of the type attributes and no semicolon is entered after the type specification.

 SQL> CREATE TYPE  employee_typ  AS OBJECT
   2    /

 Warning: Type created with compilation errors.

 SQL> CREATE TYPE  department_typ  AS OBJECT (
   2    deptno    NUMBER,
   3    manager   REF  employee_typ );

 Warning: Type created with compilation errors.

 SQL> CREATE OR REPLACE TYPE  employee_typ  AS OBJECT (
   2    empno     NUMBER,
   3    emp_name  CHAR(32),
   4    dept      REF  department_typ );

 Type created.

 SQL> ALTER TYPE  department_typ  COMPILE;

 Type altered.

After entering the above examples, you must recompile the create for the department type because the status remains invalid.
```
   SQL> ALTER TYPE  department_typ  COMPILE; 
```
It is not possible to create self-containing object types. The REF is a necessary part of the cyclic type. A REF is a logical “pointer” to a row object. It is an Oracle built-in datatype. REFs provide an easy mechanism for navigating between objects.

You must use the force option to drop a type used in a cyclic object type. For example,
```
   SQL> DROP TYPE  employee_typ  FORCE; 
```