Typical

After a presentation by Lucas Jellema I decided to try something with types in Oracle. One of the issues posed in this

presentation was that the type cannot self-reference. Neither direct nor indirect.

A table like the emp table cannot be expressed as an object type. The table has a column mgr which is a reference to another employee.

So I tried something like this:

TYPE emp_t AS OBJECT
( empno number(4)
, ename varchar2(10)
, job varchar2(9)
, mgr emp_t
, hiredate DATE
, sal number(7,2)
, comm number(7,2)
, deptno number(2)
)

This results in the following error:

Warning: Type created with compilation errors

Errors for TYPE DEMO.EMP_T:
LINE/COL ERROR
-------- ------------------------------------------------------------------------------------
1/1      PLS-00318: type "EMP_T" is malformed because it is a non-REF mutually recursive type
0/0      PL/SQL: Compilation unit analysis terminated

So, self referencing is not possible. But you can create hierarchical sets of types, where you extend one type in a child type. But the type could not include a self-reference, neither direct nor indirect.

Time to try it out. Don’t ever take for granted what anyone says, including me, always try it out.

My script was the following. I have a person type with all the information needed for this person and then, ‘below’ that I have an employee type:

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

TYPE emp_t FORCE UNDER pers_t
(
  mgr pers_t
)

And the output for these statements is:

Type created
No errors for TYPE DEMO.PERS_T
Type created
No errors for TYPE DEMO.EMP_T

Now, I know you can create objects in Oracle which cannot be called easily. Think about a package, with an overloaded function that has ambiguity in its parameters. Steven Feuerstein has a great article on this. Oracle will let you compile this package, but you can never call these programs. So, it’s time to test if it works. I created an anonymous block to try this:

DECLARE
  TYPE emps_tt IS TABLE OF emp_t INDEX BY PLS_INTEGER;
  l_emps emps_tt;
  l_mgr emp_t;
  l_emp emp_t;
  l_indx pls_integer;
BEGIN
  FOR rec IN (SELECT e.*
    FROM emp e
  CONNECT BY PRIOR e.empno = e.mgr
  STARt WITH e.mgr IS NULL) loop
    -- Check if it is the PRESIDENT
    IF rec.mgr IS NULL THEN
      l_mgr := emp_t(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
    ELSE
      l_mgr := l_emps(rec.mgr);
    END IF;
    l_emp := emp_t( rec.empno
                  , rec.ename
                  , rec.job
                  , rec.hiredate
                  , rec.sal
                  , rec.comm
                  , rec.deptno
                  , l_mgr);
    l_emps(rec.empno) := l_emp;
  END loop;
  l_indx := l_emps.first;
  LOOP
    EXIT WHEN l_indx IS NULL;
    dbms_output.put_line(l_emps(l_indx).mgr.empno || ' ' ||
                         l_emps(l_indx).mgr.ename || ' => ' ||
                         l_emps(l_indx).empno || ' ' ||
                         l_emps(l_indx).ename
                        );
    l_indx := l_emps.next(l_indx);
  END loop;
END;

And, to my surprise, it worked.

So, you cannot self-reference objects (which makes sense because the object being referenced doesn’t exist when you reference it), but you can reference parent objects.