Typical



By Patch ~ February 28th, 2014. Filed under: Oracle, PL/SQL.

After a presentation by Lucas Jellema I decided to try something with types in Oracletable_EMP. 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

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_EMP_TTYPE 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.

Reader's Comments

  1. Kim Berg Hansen | February 28th, 2014 at 3:03 pm

    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.

  2. Patch | February 28th, 2014 at 3:09 pm

    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.

  3. Kim Berg Hansen | March 5th, 2014 at 10:28 am

    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 ;-)

  4. Patch | March 5th, 2014 at 11:12 am

    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.

  5. Kim Berg Hansen | March 5th, 2014 at 1:41 pm

    The same as in my first comment on this thread?

  6. Patch | March 5th, 2014 at 3:11 pm

    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.

  7. Kim Berg Hansen | March 5th, 2014 at 3:53 pm

    Great, then minimum version for my next SQL quiz is set correctly ;-) Thanks for the test.

Leave a Comment