By Patch ~ February 28th, 2014. Filed under: Oracle, PL/SQL.
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 include 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 createdNo errors for TYPE DEMO.PERS_TType createdNo 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's 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.