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 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'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.
Interesting trick, Pat, nice 🙂
An alternative would be using REFs to objects. Using MAKE_REF it is possible to self-reference. At least this works in my 12.1 test base:
create type emp_t as object (
empno number(4),
ename varchar2(10),
job varchar2(9),
mgr REF emp_t,
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
)
/
create or replace FORCE view emp_view
of emp_t
with object identifier (empno)
as
select e.empno
, e.ename
, e.job
, MAKE_REF(emp_view,e.mgr)
, e.hiredate
, e.sal
, e.comm
, e.deptno
from emp e
/
select e.empno
, e.ename
, e.mgr.ename mgr_name
, e.mgr.mgr.ename grand_mgr
, e.mgr.mgr.mgr.ename great_grand_mgr
from emp_view e
order by e.empno
/
EMPNO ENAME MGR_NAME GRAND_MGR GREAT_GRAN
———- ———- ———- ———- ———-
7369 SMITH FORD JONES KING
7499 ALLEN BLAKE KING
7521 WARD BLAKE KING
7566 JONES KING
7654 MARTIN BLAKE KING
7698 BLAKE KING
7782 CLARK KING
7788 SCOTT JONES KING
7839 KING
7844 TURNER BLAKE KING
7876 ADAMS SCOTT JONES KING
7900 JAMES BLAKE KING
7902 FORD JONES KING
7934 MILLER CLARK KING
The FORCE keyword in create view is necessary, or it will complain that emp_view does not exist yet.
I have not tested that yet. I was running an Oracle 11Gr2 database for this. I think 12c has not penetrated the market as much as 11G. Maybe (and I didn’t test that either) my approach also works on 10G.
Just FYI I have tested the REF approach on 11.2 and it works. If docs are to be believed, it should also work in 10G as well 😉
If you provide me with the script you used on the 11Gr2 database, I can probably try to run it on my 10.2 image.
The same as in my first comment on this thread?
I ran the script on my 10.2.0.1.0 Virtual Box image and it worked flawlessly.
And the script I used in the post works as well as long as the keyword force is removed in the type creation.
Great, then minimum version for my next SQL quiz is set correctly 😉 Thanks for the test.