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.

view source
print?
01SQL> CREATE TYPE  employee_typ  AS OBJECT
02  2    /
03 
04Warning: Type created with compilation errors.
05 
06SQL> CREATE TYPE  department_typ  AS OBJECT (
07  2    deptno    NUMBER,
08  3    manager   REF  employee_typ );
09 
10Warning: Type created with compilation errors.
11 
12SQL> CREATE OR REPLACE TYPE  employee_typ  AS OBJECT (
13  2    empno     NUMBER,
14  3    emp_name  CHAR(32),
15  4    dept      REF  department_typ );
16 
17Type created.
18 
19SQL> ALTER TYPE  department_typ  COMPILE;
20 
21Type 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.