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;



      I came up with a new word yesterday:    
      Plagiarism.