0 
0 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.
Related posts:
PL/SQL vs SQL 
How-to set the current database schema of an application using a global context Death of the End Date – How LAG and LEAD help fight redundancy 
Oracle releases Raptor – free tool for Database Development (SQL, PL/SQL, database object browsing) OOW 2009: Introducing SOA and Oracle SOA Suite 11g for Database Professionals
Patrick,
sometimes it might be more convenient to use just some member function, which will return manager
Just simple example: http://gist.github.com/xtender/9355581
Lucas already promised to change his presentation after I’d shown him a similar example 🙂