Typical

2
Share this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

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

table_EMP

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.

type_EMP

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.

type_PERS_T_EMP_T

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.

Share this on .. Tweet about this on Twitter0Share on LinkedIn0Share on Facebook0Share on Google+0Email this to someoneShare on TumblrBuffer this page

About Author

Patrick Barel is a PL/SQL Developer for AMIS Services in the Netherlands. Besides working with SQL and PL/SQL did he co-develop CodeGen and wrote different plug-ins for PL/SQL Developer. He publishes articles on the AMIS Technology blog and on his own blog http://blog.bar-solutions.com/.

2 Comments

Leave a Reply