Object Relational Views

This is a fairly powerful feature for those of you who want to work with the object relational features, but still must present a relational view of the data to many applications. This allows you to use the standard VIEW mechanism to synthesize objects from relational tables. You don't have to create tables of a TYPE, with all of the mysterious columns and such – you can create a view of standard tables you have created (and probably already have). These views will behave just like an object table of that type would – without much of the overhead of hidden keys, surrogate keys, and other nuances.

In this section, we will use the EMP and DEPT tables to present a department-oriented view of the data. This is similar to the example of the nested table we used in Chapter 6 on Tables, where by we had the EMP_TAB_TYPE as a nested table of EMP_TYPE, and the DEPT table had a column of this nested table type. Here, we will model the EMP_TYPE and the EMP_TAB_TYPE once again, but we will also create a DEPT_TYPE object type as well and a view of that type.

It is interesting to note that this approach of using object views allows us to have the best of both worlds (relational and object relational). For example, we might have an application that needs a department-oriented view of the data. Their view starts at the department and employees in the department are naturally modeled as a collection inside the department. Another application however needs a different perspective. For example, when you walk up to a security guard and identify yourself as an employee, they will need to have an employee-oriented view of the data. Department in this case is inferred by the employee, not the other way around where the view was that department infers employees. This is the power of the relational model – many different views can efficiently be supported simultaneously. The object model does not support many different views of the same data as easily (if at all) or efficiently. By using many different object views of the relational data, we can satisfy everyone.
The Types
The types used in this example are borrowed from Chapter 6, with the addition of the DEPT_TYPE. They are:

 
scott@TKYTE816> create or replace type emp_type
  2  as object
  3  (empno       number(4),
  4   ename       varchar2(10),
  5   job         varchar2(9),
  6   mgr         number(4),
  7   hiredate    date,
  8   sal         number(7, 2),
  9   comm        number(7, 2)
 10  );
 11  /
Type created.

scott@TKYTE816>
scott@TKYTE816> create or replace type emp_tab_type
  2  as table of emp_type
  3  /
Type created.

scott@TKYTE816> create or replace type dept_type
  2  as object
  3  ( deptno number(2),
  4    dname  varchar2(14),
  5    loc       varchar2(13),
  6    emps      emp_tab_type
  7  )
  8  /
Type created.

Once again, a department is modeled as being an object with a department number, a name, a location, and employees.
The O-R View
From the above type definitions, it is very easy to synthesize our data for this view from the existing relational data. It would look like:

scott@TKYTE816> create or replace view dept_or
  2  of dept_type
  3  with object identifier(deptno)
  4  as
  5  select deptno, dname, loc,
  6         cast ( multiset (
  7                 select empno, ename, job, mgr, hiredate, sal, comm
  8                   from emp
  9                  where emp.deptno = dept.deptno )
 10                as emp_tab_type )
 11    from dept
 12  /
View created.

We are already familiar with the role of the CAST and the MULTISET – we are just turning a correlated subquery into a nested table collection here. For each row in DEPT, we'll query out all of the employees. We've told Oracle which column(s) identify a row uniquely in the view using the WITH OBJECT IDENTIFIER clause. This allows Oracle to synthesize an object reference for us, giving us the ability to treat this view as if it were an object table.

As soon as we have the view, we can start using it:

scott@TKYTE816> select dname, d.emps
  2    from dept_or d
  3  /

DNAME          EMPS(EMPNO, ENAME, JOB, MGR, HIREDATE, S
-------------- ----------------------------------------
ACCOUNTING     EMP_TAB_TYPE(EMP_TYPE(7782, 'CLARK',
               'MANAGER', 7839, '09-JUN-81', 2450,
               NULL), EMP_TYPE(7839, 'KING',
               'PRESIDENT', NULL, '17-NOV-81', 5000,
               NULL), EMP_TYPE(7934, 'MILLER', 'CLERK',
               7782, '23-JAN-82', 1300, NULL))

RESEARCH       EMP_TAB_TYPE(EMP_TYPE(7369, 'SMITH',
               'CLERK', 7902, '17-DEC-80', 800, NULL),
               EMP_TYPE(7566, 'JONES', 'MANAGER', 7839,
               '02-APR-81', 2975, NULL), EMP_TYPE(7788,
               'SCOTT', 'ANALYST', 7566, '09-DEC-82',
               3000, NULL), EMP_TYPE(7876, 'ADAMS',
               'CLERK', 7788, '12-JAN-83', 1100, NULL),
               EMP_TYPE(7902, 'FORD', 'ANALYST', 7566,
               '03-DEC-81', 3000, NULL))

SALES          EMP_TAB_TYPE(EMP_TYPE(7499, 'ALLEN',
               'SALESMAN', 7698, '20-FEB-81', 1600,
               300), EMP_TYPE(7521, 'WARD', 'SALESMAN',
               7698, '22-FEB-81', 1250, 500),
               EMP_TYPE(7654, 'MARTIN', 'SALESMAN',
               7698, '28-SEP-81', 1250, 1400),
               EMP_TYPE(7698, 'BLAKE', 'MANAGER', 7839,
               '01-MAY-81', 2850, NULL), EMP_TYPE(7844,
               'TURNER', 'SALESMAN', 7698, '08-SEP-81',
               1500, 0), EMP_TYPE(7900, 'JAMES',
               'CLERK', 7698, '03-DEC-81', 950, NULL))

OPERATIONS     EMP_TAB_TYPE()

4 rows selected.

scott@TKYTE816> select deptno, dname, loc, count(*)
  2    from dept_or d, table ( d.emps )
  3   group by deptno, dname, loc
  4  /

    DEPTNO DNAME          LOC             COUNT(*)
---------- -------------- ------------- ----------
        10 ACCOUNTING     NEW YORK               3
        20 RESEARCH       DALLAS                 5
        30 SALES          CHICAGO                6

3 rows selected.

So, we are on our way. We have the relational tables and the object relational view. Externally, it is difficult to tell which is the view, and which are the tables. The functionality of an object table is available to us – we have object references on this table, the nested table is set up, and so on. The advantage here is that we specify how to join EMP to DEPT using the existing natural parent/child relationship.

So, we have created an object relational view that exposes the data for querying. It does not however work when it comes to modifications yet:

scott@TKYTE816> update TABLE ( select p.emps
  2                   from dept_or p
  3                  where deptno = 20 )
  4     set ename = lower(ename)
  5  /
   set ename = lower(ename)
       *
ERROR at line 4:
ORA-25015: cannot perform DML on this nested table view column


scott@TKYTE816> declare
  2     l_emps  emp_tab_type;
  3  begin
  4      select p.emps into l_emps
  5        from dept_or  p
  6       where deptno = 10;
  7
  8      for i in 1 .. l_emps.count
  9      loop
 10          l_emps(i).ename := lower(l_emps(i).ename);
 11      end loop;
 12
 13      update dept_or
 14         set emps = l_emps
 15       where deptno = 10;
 16  end;
 17  /
declare
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
ORA-06512: at line 13

We need to 'train' our view how to update itself. We have a somewhat complex mapping of relational data to object relational – it can be arbitrarily complex in fact. So, how can we 'train' our view to update itself? Oracle provides a mechanism called an INSTEAD OF trigger for this purpose. We can code the logic that should execute INSTEAD OF Oracles logic when we modify the contents of the view. For illustrative purposes, we will train the above view to allow it to update itself.

Oracle allows us to place instead of triggers on the view DEPT_OR as well as any nested table type included in the view. If we place a trigger on the nested table columns, it will allow us to process the first update from above – the update of the nested table column as if it where a table. The trigger for that would look like:

scott@TKYTE816> create or replace trigger EMPS_IO_UPDATE
  2  instead of UPDATE on nested table emps of dept_or
  3  begin
  4      if ( :new.empno = :old.empno )
  5      then
  6          update emp
  7             set ename = :new.ename, job = :new.job, mgr = :new.mgr,
  8              hiredate = :new.hiredate, sal = :new.sal, comm = :new.comm
  9           where empno = :old.empno;
 10      else
 11          raise_application_error(-20001,'Empno cannot be updated' );
 12      end if;
 13  end;
 14  /

Trigger created.

As you can see, this trigger will fire instead of update on the nested table column EMPS of the DEPT_OR view. It will be called for each and every row modified in the nested table and has access to the :OLD and :NEW values – just like a 'normal' trigger would. In this case, it is clear what we need to do. We need to update the existing EMP row by EMPNO, setting the columns to their new values. One thing I enforce in this trigger is that an update to the primary key is not allowed (hey, we might be using object relational features, but that doesn’t mean we should violate the basic tenets of relational database design!).

Now if we execute:

scott@TKYTE816> update TABLE ( select p.emps
  2                   from dept_or p
  3                  where deptno = 20 )
  4     set ename = lower(ename)
  5  /
5 rows updated.

scott@TKYTE816> select ename from emp where deptno = 20;

ENAME
----------
smith
jones
scott
adams
ford

scott@TKYTE816> select ename
  2    from TABLE( select p.emps
  3                  from dept_or p
  4                 where deptno = 20 );

ENAME
----------
smith
jones
scott
adams
ford

We see that the update of the nested table successfully translates into the relational table updates as expected. Coding the relevant INSERT and DELETE triggers are equally as easy – the update is the most complex case so we'll stop there.

At this point, it we execute:

scott@TKYTE816> declare
  2     l_emps  emp_tab_type;
  3  begin
  4      select p.emps into l_emps
  5        from dept_or  p
  6       where deptno = 10;
  7
  8      for i in 1 .. l_emps.count
  9      loop
 10          l_emps(i).ename := lower(l_emps(i).ename);
 11      end loop;
 12
 13      update dept_or
 14         set emps = l_emps
 15       where deptno = 10;
 16  end;
 17  /
declare
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view
ORA-06512: at line 13

We find that it still does not work. This might seem surprising. Shouldn't it fire our trigger from above? In fact, no it will not. Only updates to the nested table that use the collection un-nesting will fire that trigger – only when we treat the nested table as a table will that trigger fire. Here, we are simply updating a column in the DEPT_OR table – we are not doing a set operation on the nested table itself. In order to support this style of code (and to support updates to the other scalar attributes of the DEPT_OR view itself), we need an instead of trigger on the DEPT_OR view. The logic of this trigger will be to process the :OLD.EMPS and :NEW.EMPS as sets and:

1. Remove ALL records from EMP such that their EMPNO was in the :OLD set but is not seen in the :NEW set.  The SQL operator MINUS is excellent for this.
2. Update every EMP record such that their empno is in the set of empnos such that their :NEW record differs from their :OLD record.  Again MINUS is excellent for finding that set easily.
3. INSERT into EMP all :NEW records where the EMPNO of the :NEW record was not in the :OLD set of values.

This implementation is:

scott@TKYTE816> create or replace trigger DEPT_OR_IO_UPDATE
  2  instead of update on dept_or
  3  begin
  4      if ( :new.deptno = :old.deptno )
  5      then
  6          if updating('DNAME') or updating('LOC')
  7          then
  8             update dept
  9                set dname = :new.dname, loc = :new.loc
 10              where deptno = :new.deptno;
 11          end if;
 12
 13          if ( updating('EMPS') )
 14          then
 15             delete from emp
 16              where empno in
 17              ( select empno
 18                  from TABLE(cast(:old.emps as emp_tab_type))
 19                 MINUS
 20                  select empno
 21                    from TABLE(cast(:new.emps as emp_tab_type))
 22              );
 23             dbms_output.put_line( 'deleted ' || sql%rowcount );

This first MINUS gives us the set of empno's that were in the :OLD but are not seen in the :NEW set, we need to remove those records from the EMP table as they no longer exist in the collection.  Next we’ll modify the changed collection records:
 
 24
 25             update emp E
 26                set ( deptno, ename, job, mgr,
 27                      hiredate, sal, comm ) =
 28                    ( select :new.deptno, ename, job, mgr,
 29                             hiredate, sal, comm
 30                        from TABLE(cast(:new.emps as emp_tab_type)) T
 31                       where T.empno = E.empno
 32                    )
 33              where empno in
 34                ( select empno
 35                    from (select *
 36                            from TABLE(cast(:new.emps as emp_tab_type))
 37                            MINUS
 38                           select *
 39                             from TABLE(cast(:old.emps as emp_tab_type))
 40                          )
 41                );
 42             dbms_output.put_line( 'updated ' || sql%rowcount );

That minus returned everything in NEW minus anything in OLD – that’s the set of modified records.  We used that in a subquery to get the set of EMPNO’s we need to update in the EMP table and then used a correlated subquery to actually set those values.  Lastly, we’ll add all new records:

 43
 44             insert into emp
 45             ( deptno, empno, ename, job, mgr, hiredate, sal, comm )
 46             select :new.deptno,empno,ename,job,mgr,hiredate,sal,comm
 47               from ( select *
 48                        from TABLE(cast(:new.emps as emp_tab_type))
 49                       where empno in
 50                          ( select empno
 51                              from TABLE(cast(:new.emps as emp_tab_type))
 52                              MINUS
 53                            select empno
 54                              from TABLE(cast(:old.emps as emp_tab_type))
 55                          )
 56                    );
 57             dbms_output.put_line( 'inserted ' || sql%rowcount );
 58          else
 59             dbms_output.put_line( 'Skipped processing nested table' );
 60          end if;
 61      else
 62          raise_application_error(-20001,'deptno cannot be udpated' );
 63      end if;
 64  end;
 65  /
Trigger created.

That minus generated the set of EMPNO’s in the NEW collection that were not present in the OLD collection – this presents a list of rows to add to EMP.

This looks like a monster trigger, but it is actually straightforward. To recap, it begins by seeing if the scalar columns of DEPT_OR were modified. If so, it applies the changes to the DEPT table. Next, if the nested table column was updated (all of its values replaced), it reflects those changes to the EMP table. What we need to do to reflect those changes is:

1. Delete any records in EMP that were removed from the EMPS nested table column.
2. Update any records in EMP that had their values modified in the EMPS nested table column.
3. Insert any records into EMP that were added to the EMPS nested table column.

Fortunately, the SQL MINUS operator and the ability to TABLE the nested column variable make this easy for us. Now we can process:

scott@TKYTE816> declare
  2     l_emps  emp_tab_type;
  3  begin
  4      select p.emps into l_emps
  5        from dept_or  p
  6       where deptno = 10;
  7
  8      for i in 1 .. l_emps.count
  9      loop
 10          l_emps(i).ename := lower(l_emps(i).ename);
 11      end loop;
 12
 13      update dept_or
 14         set emps = l_emps
 15       where deptno = 10;
 16  end;
 17  /
deleted 0
updated 3
inserted 0

PL/SQL procedure successfully completed.

scott@TKYTE816> declare
  2     l_emps  emp_tab_type;
  3  begin
  4      select p.emps into l_emps
  5        from dept_or  p
  6       where deptno = 10;
  7
  8
  9      for i in 1 .. l_emps.count
 10      loop
 11          if ( l_emps(i).ename = 'miller' )
 12          then
 13              l_emps.delete(i);
 14          else
 15              l_emps(i).ename := initcap( l_emps(i).ename );
 16          end if;
 17      end loop;
 18
 19      l_emps.extend;
 20      l_emps(l_emps.count) :=
 21         emp_type(1234, 'Tom', 'Boss',
 22                   null, sysdate, 1000, 500 );
 23
 24      update dept_or
 25         set emps = l_emps
 26       where deptno = 10;
 27  end;
 28  /
deleted 1
updated 2
inserted 1

PL/SQL procedure successfully completed.

scott@TKYTE816> update dept_or set dname = initcap(dname);
Skipped processing nested table
Skipped processing nested table
Skipped processing nested table
Skipped processing nested table

4 rows updated.

scott@TKYTE816> commit;
Commit complete.

and our trigger translates our operations on the object instance into the equivalent modifications against the base relational tables.

This capability, to expose our relational data as object relational views, allows us to maximize the benefits of both the relational model and the object relational model.

The relational model shows its strength in its ability to answer almost any question you might have of the underlying data, easily and efficiently. Whether your view of the data is a departmental view (query a department and related employees) or employee-oriented (you specify an employee number and need to see departmental information), we can support you. You can use the relational tables directly or we can generate an object type model that exposes your view of the data, pulls all of the requisite data together, and gives it to you easily. Consider the results of these two queries:

scott@TKYTE816> select * from dept_or where deptno = 10;

    DEPTNO DNAME          LOC           EMPS(EMPNO, ENAME, JOB, MGR, HIREDATE, S
---------- -------------- ------------- ----------------------------------------
        10 Accounting     NEW YORK      EMP_TAB_TYPE(EMP_TYPE(7782, 'Clark',
                                        'MANAGER', 7839, '09-JUN-81', 2450,
                                        NULL), EMP_TYPE(7839, 'King',
                                        'PRESIDENT', NULL, '17-NOV-81', 5000,
                                        NULL), EMP_TYPE(1234, 'Tom', 'Boss',
                                        NULL, '25-MAR-01', 1000, 500)) 
scott@TKYTE816> select dept.*, empno, ename, job, mgr, hiredate, sal, comm
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  and dept.deptno = 10
  5  /

    DEPTNO DNAME       LOC      EMPNO ENAME JOB         MGR HIREDATE    SAL  COMM
---------- ----------- -------- ----- ----- --------- ----- --------- ----- -----
        10 Accounting  NEW YORK  7782 Clark MANAGER    7839 09-JUN-81  2450
        10 Accounting  NEW YORK  7839 King  PRESIDENT       17-NOV-81  5000
        10 Accounting  NEW YORK  1234 Tom   Boss            25-MAR-01  1000   500

They return similar data. The first one concisely gives you all of the information about a department in a single row. It could come back with many nested table types, which in SQL would take many queries. It can do a lot of work in the server assembling the answer for you, and shipping it back in a single row. If you are in an environment where network round trips are to be avoided when possible (long latency times) this could be extremely beneficial. Not to mention that a single select * from t can do the work of many SQL statements. Also, notice that repeating columns of data don't occur with the object view. The DEPTNO, DNAME, and LOC columns are not repeated for each employee; they are returned once, which may be more intuitive for many applications.

The second query requires the developer to have more knowledge of the data (that's not a bad thing mind you, just something to consider). They must know how to join the data together and, if there were many other tables to join to, they might very well need many separate queries they would have to assemble back together themselves to get the same answer. As a matter of example by what I mean by that, suppose in your model that a department has a fiscal year budget. It is stored relationally as:

scott@TKYTE816> create table dept_fy_budget
  2  ( deptno   number(2) references dept,
  3    fy       date,
  4    amount   number,
  5    constraint dept_fy_budget_pk primary key(deptno,fy)
  6  )
  7  /

Table created.

You have some data in there representing this year and the last couple of years FY budgets by department. Your application needs the department view that has all of the scalar data surrounding the department (name, location). It also needs the employee related information (the EMP_TAB_TYPE). It also however, needs the FY budget information as well. In order to get that relationally, the application programmer will have to code:

scott@TKYTE816> select dept.*, empno, ename, job, mgr, hiredate, sal, comm
  2  from emp, dept
  3  where emp.deptno = dept.deptno
  4  and dept.deptno = 10
  5  /

    DEPTNO DNAME       LOC      EMPNO ENAME JOB         MGR HIREDATE    SAL  COMM
---------- ----------- -------- ----- ----- --------- ----- --------- ----- -----
        10 Accounting  NEW YORK  7782 Clark MANAGER    7839 09-JUN-81  2450
        10 Accounting  NEW YORK  7839 King  PRESIDENT       17-NOV-81  5000
        10 Accounting  NEW YORK  1234 Tom   Boss            25-MAR-01  1000   500

3 rows selected.

scott@TKYTE816> select fy, amount
  2  from dept_fy_budget
  3  where deptno = 10
  4  /

FY            AMOUNT
--------- ----------
01-JAN-99        500
01-JAN-00        750
01-JAN-01       1000

3 rows selected.

It is not possible to write a single relational querythat retrieves that data in one call.  We can use some Oracle extensions (the CURSOR function in SQL) to return rows that return result sets themselves:

scott@TKYTE816> select
  2 dept.deptno, dept.dname,
  3 cursor(select empno from emp where deptno = dept.deptno),
  4 cursor(select fy, amount from dept_fy_budget where deptno = dept.deptno)
  5   from dept
  6  where deptno = 10
  7  /

DEPTNO DNAME          CURSOR(SELECTEMPNOFR CURSOR(SELECTFY,AMOU
------ -------------- -------------------- --------------------
    10 ACCOUNTING     CURSOR STATEMENT : 3 CURSOR STATEMENT : 4

CURSOR STATEMENT : 3

     EMPNO
----------
      7782
      7839
      7934

3 rows selected.

CURSOR STATEMENT : 4

FY            AMOUNT
--------- ----------
01-JAN-99        500
01-JAN-00        750
01-JAN-01       1000

3 rows selected.

1 row selected.

In this case – 1 row was selected and that row returned two more cursors to the client.  The client fetched from each cursor and displayed the results.  This works nicely, but requires a knowledge of the underlying data and how to put it together (how to write the correlated subqueries to generate the cursors).  We can instead model this data using the object relational extensions and recreate our view as follows:

scott@TKYTE816> create or replace type dept_budget_type
  2  as object
  3  ( fy        date,
  4    amount number
  5  )
  6  /
Type created.

scott@TKYTE816> create or replace type dept_budget_tab_type
  2  as table of dept_budget_type
  3  /
Type created.

scott@TKYTE816> create or replace type dept_type
  2  as object
  3  ( deptno number(2),
  4    dname  varchar2(14),
  5    loc       varchar2(13),
  6    emps      emp_tab_type,
  7    budget dept_budget_tab_type
  8  )
  9  /
Type created.

scott@TKYTE816> create or replace view dept_or
  2  of dept_type
  3  with object identifier(deptno)
  4  as
  5  select deptno, dname, loc,
  6         cast ( multiset (
  7                 select empno, ename, job, mgr, hiredate, sal, comm
  8                   from emp
  9                  where emp.deptno = dept.deptno )
 10                as emp_tab_type ) emps,
 11         cast ( multiset (
 12                 select fy, amount
 13                   from dept_fy_budget
 14                  where dept_fy_budget.deptno = dept.deptno )
 15                as dept_budget_tab_type ) budget
 16    from dept
 17  /

View created.

Now remember, the above is work we do once, the complexity is hidden from the application. The application will simply code:

scott@TKYTE816> select * from dept_or where deptno = 10
  2  /

    DEPTNO DNAME       LOC      EMPS(EMPNO, ENAME, J BUDGET(FY, AMOUNT)
---------- ----------- -------- -------------------- --------------------
        10 Accounting  NEW YORK EMP_TAB_TYPE(EMP_TYP DEPT_BUDGET_TAB_TYPE
                                E(7782, 'Clark',     (DEPT_BUDGET_TYPE('0
                                'MANAGER', 7839,     1-JAN-99', 500),
                                '09-JUN-81', 2450,   DEPT_BUDGET_TYPE('01
                                NULL),               -JAN-00', 750),
                                EMP_TYPE(7839,       DEPT_BUDGET_TYPE('01
                                'King', 'PRESIDENT', -JAN-01', 1000))
                                NULL, '17-NOV-81',
                                5000, NULL),
                                EMP_TYPE(1234,
                                'Tom', 'Boss', NULL,
                                '25-MAR-01', 1000,
                                500))


1 row selected.

Again, they get back one row, one object instance, that represents their view of the data. This can be quite handy indeed. The complexity of the underlying physical model is removed and it is easy to see how you might populate a GUI screen with this data.  Languages such as Java via JDBC, Visual Basic with OO4O (Oracle Objects for Ole), OCI (Oracle Call Interface), PL/SQL, and Pro*C can all make use of this functionality easily.  Using the relational model, it is a little more cumbersome as you get more and more complex one to many relationships. With the object relational model, it is a little more natural. Of course, we would have to modify our INSTEAD OF triggers to support modifications to the underlying relational data as well, so it is not totally completed but the gist is there.