Oracle Object Relational Views Type Varray Nested Table Example Sample Code Demos Tutorials

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

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;