Oracle Object-Relational Views
Version 11.2.0.3

General Information
It can be said as a general rule that nested table, object tables, are almost always a really bad idea. So if you want to present your data as objects, while retaining the benefits of relational storage, Object Relatoinal Views are the best way to meet both objectives.
Data Dictionary Objects
ALL_VIEWS DBA_VIEWS USER_VIEWS
ALL_VIEWS_AE DBA_VIEWS_AE USER_VIEWS_AE
 
Object View Demo
Object View Demo CREATE OR REPLACE VIEW <schema_name>.<view_name> OF <schema_name>.<type_name>
WITH OBJECT IDENTIFIER (<column_name>) AS
<SELECT statement>;
CREATE TABLE dept (
deptno  NUMBER(2),
dname   VARCHAR2(14),
loc     VARCHAR2(13));

ALTER TABLE dept
ADD CONSTRAINT pk_dept
PRIMARY KEY (deptno)
DEFERRABLE INITIALLY DEFERRED
USING INDEX;

CREATE TABLE emp (
empno     NUMBER(4),
ename     VARCHAR2(10),
job       VARCHAR2(9),
mgr       NUMBER(4),
hiredate  DATE,
sal       NUMBER(7,2),
comm      NUMBER(7,2),
deptno    NUMBER(2));

ALTER TABLE emp
ADD CONSTRAINT pk_emp
PRIMARY KEY (empno)
DEFERRABLE INITIALLY DEFERRED
USING INDEX;

ALTER TABLE emp
ADD CONSTRAINT fk_emp_deptno
FOREIGN KEY (deptno)
REFERENCES dept (deptno)
DEFERRABLE INITIALLY DEFERRED;

INSERT INTO dept VALUES (1, 'EM', 'Seattle');
INSERT INTO dept VALUES (2, 'IT', 'San Francisco');

INSERT INTO emp
VALUES (1, 'Morgan', 'CIO', NULL, SYSDATE, 72500, 0, 1);

INSERT INTO emp
VALUES (2, 'Townsend', 'CFO', NULL, SYSDATE, 65250, 6, 1);
COMMIT;

CREATE OR REPLACE TYPE emp_type AUTHID DEFINER AS OBJECT (
empno     NUMBER(4),
ename     VARCHAR2(10),
job       VARCHAR2(9),
mgr       NUMBER(4),
hiredate  DATE,
sal       NUMBER(7, 2),
comm      NUMBER(7, 2));
/

CREATE OR REPLACE TYPE emp_tab_type
AS TABLE OF emp_type;
/

CREATE OR REPLACE TYPE dept_type AUTHID DEFINER AS OBJECT (
deptno  NUMBER(2),
dname   VARCHAR2(14),
loc     VARCHAR2(13),
emps    emp_tab_type);
/

CREATE OR REPLACE VIEW dept_orv OF dept_type
WITH OBJECT IDENTIFIER (deptno) AS
SELECT deptno, dname, loc, CAST(MULTISET(
  SELECT empno, ename, job, mgr, hiredate, sal, comm
  FROM emp
  WHERE emp.deptno = dept.deptno ) AS emp_tab_type)
FROM dept;
/

set describe depth all
desc dept_orv

col emps format a80

SELECT dname, d.emps AS emps
FROM dept_orv d;

SELECT deptno, dname, loc, COUNT(*)
FROM dept_orv d, table (d.emps)
GROUP BY deptno, dname, loc;

UPDATE dept_orv
SET dname = 'Research'
WHERE deptno = 2;

ROLLBACK;

-- this will produce an error
UPDATE TABLE (
  SELECT p.emps
  FROM dept_orv p
  WHERE deptno = 1)
SET ename = LOWER(ename);

CREATE OR REPLACE TRIGGER emps_io_update
INSTEAD OF UPDATE
ON NESTED TABLE emps OF dept_orv
BEGIN
  IF (:new.empno = :old.empno) THEN
    UPDATE emp
    SET ename = :new.ename, job = :new.job, mgr = :new.mgr,
    hiredate = :new.hiredate, sal = :new.sal, comm = :new.comm
    WHERE empno = :old.empno;
  ELSE
    raise_application_error(-20001,'Empno cannot be updated');
  END IF;
END;
/

UPDATE TABLE (
  SELECT p.emps
  FROM dept_orv p
  WHERE deptno = 1 )
SET ename = LOWER(ename);

COMMIT;

SELECT * FROM dept;
SELECT * FROM emp;
SELECT * FROM dept_or;

Related Topics
Instead Of Trigger
Nested Tables
Object Tables
Types
Views

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved