You can define circular references in object views using the
view_A can refer to
view_B which in turn can refer to
view_A. This allows an object view to synthesize a complex structure such as a graph from relational data.
For example, in the case of the department and employee, the department object currently includes a list of employees. To conserve space, you may want to put references to the employee objects inside the department object, instead of materializing all the employees within the department object. You can construct (pin) the references to employee objects, and later follow the references using the dot notation to extract employee information.
Because the employee object already has a reference to the department in which the employee works, an object view over this model contains circular references between the department view and the employee view.
You can create circular references between object views in two different ways:
Both ways to create circular references require the setup described in "Tables and Types to Demonstrate Circular View References".
First View After Second View
Create view A without any reference to view B.
Create view B, which includes a reference to view A.
Replace view A with a new definition that includes the reference to view B.
See the example in "Method 1: Re-create First View After Creating Second View"
First View Using the
Create view A with a reference to view B using the
Create view B with a reference to view A. When view A is used, it is validated and re-compiled.
See the example in "Method 2: Create First View Using FORCE Keyword"
Method 2 has fewer steps, but the
FORCE keyword may hide errors in the view creation. You need to query the
USER_ERRORS catalog view to see if there were any errors during the view creation. Use this method only if you are sure that there are no errors in the view creation statement.
Also, if errors prevent the views from being recompiled upon use, you must recompile them manually using the
Perform the setup described next before attempting to use either method of creating circular view references.
6.13.1 Tables and Types to Demonstrate Circular View References
First, you need set up some relational tables and associated object types. Although the tables contain some objects, they are not object tables. To access the data objects, you will create object views later.
emp table stores the employee information:
Example 6-10 Creating emp table to demonstrate circular references
CREATE TABLE emp ( empno NUMBER PRIMARY KEY, empname VARCHAR2(20), salary NUMBER, deptno NUMBER ); -- first create a dummy, that is, incomplete, department type, so emp_t type -- created later will succeed CREATE TYPE dept_t; / -- Create the employee type with a reference to the department,
dept_t: CREATE TYPE emp_t AS OBJECT ( eno NUMBER, ename VARCHAR2(20), salary NUMBER, deptref REF dept_t ); / -- Represent the list of references to employees as a nested table: CREATE TYPE employee_list_ref_t AS TABLE OF REF emp_t; / -- Create the department table as a relational table CREATE TABLE dept ( deptno NUMBER PRIMARY KEY, deptname VARCHAR2(20), deptstreet VARCHAR2(20), deptcity VARCHAR2(10), deptstate CHAR(2), deptzip VARCHAR2(10) ); -- Create object types that map to columns from the relational tables: CREATE TYPE address_t AS OBJECT ( street VARCHAR2(20), city VARCHAR2(10), state CHAR(2), zip VARCHAR2(10)); / -- Fill in the definition for
dept_t, the incomplete type you previously created: CREATE OR REPLACE TYPE dept_t AS OBJECT ( dno NUMBER, dname VARCHAR2(20), deptaddr address_t, empreflist employee_list_ref_t); /
As Example 6-10 indicates, you must create the
emp table, then create a dummy department type,
dept_t which will enable the
emp_t type to succeed once you create it. After that, create
emp_t with a reference to
dept_t. Create a list of references to employees as a nested table,
employee_list_ref_t and create the department table,
dept. Then create an object type,
address_t that has columns mapping to the relational tables, and finally fill in the definition for the incomplete
The following is example data you could use:
insert into emp values(1,'John','900',100); insert into emp values(2,'james','1000',100); insert into emp values(3,'jack',2000,200);
6.13.2 Creating Object Views with Circular References
You can create object views with circular references.
If you have established the underlying relational table definitions, as described in Defining Complex Relationships in Object Views, you can create the object views on top of them.
188.8.131.52 Method 1: Re-create First View After Creating Second View
You can recreate the first view after creating the second view.
First create the employee view with a null in the
deptref column. Later, you can turn that column into a reference.
Next, create the department view, which includes references to the employee objects. This creates a list of references to employee objects, instead of including the entire employee object.
Next, re-create the employee view with the reference to the department view.
Example 6-11 Creating an Object View with a Circular Reference, Method 1
-- Requires Ex. 6-10 CREATE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno) AS SELECT e.empno, e.empname, e.salary, NULL FROM emp e; -- create department view, including references to the employee objects CREATE VIEW dept_view OF dept_t WITH OBJECT IDENTIFIER(dno) AS SELECT d.deptno, d.deptname, address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip), CAST( MULTISET ( SELECT MAKE_REF(emp_view, e.empno) FROM emp e WHERE e.deptno = d.deptno) AS employee_list_ref_t) FROM dept d;
CREATE OR REPLACE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno) AS SELECT e.empno, e.empname, e.salary, MAKE_REF(dept_view, e.deptno) FROM emp e;
This creates the views.
184.108.40.206 Method 2: Create First View Using FORCE Keyword
You can force creation of a first view even if the other view does not yet exist.
If you are sure that the view creation statement has no syntax errors, you can use the
FORCE keyword to force the creation of the first view without the other view being present.
First, create an employee view that includes a reference to the department view, which does not exist at this point. This view cannot be queried until the department view is created properly.
Next, create a department view that includes references to the employee objects. You do not have to use the
FORCE keyword here, because
emp_view already exists. This allows you to query the department view, getting the employee object by dereferencing the employee reference from the nested table
Example 6-12 Creating view with FORCE Method 2
-- Requires Ex. 6-10 -- create employee view CREATE OR REPLACE FORCE VIEW emp_view OF emp_t WITH OBJECT IDENTIFIER(eno) AS SELECT e.empno, e.empname, e.salary, MAKE_REF(dept_view, e.deptno) FROM emp e; -- create a department view that includes references to the employee objects CREATE OR REPLACE VIEW dept_view OF dept_t WITH OBJECT IDENTIFIER(dno) AS SELECT d.deptno, d.deptname, address_t(d.deptstreet,d.deptcity,d.deptstate,d.deptzip), CAST( MULTISET ( SELECT MAKE_REF(emp_view, e.empno) FROM emp e WHERE e.deptno = d.deptno) AS employee_list_ref_t) FROM dept d; -- Querying with DEREF method SELECT DEREF(e.COLUMN_VALUE) FROM TABLE( SELECT e.empreflist FROM dept_view e WHERE e.dno = 100) e;
COLUMN_VALUE is a special name that represents the scalar value in a scalar nested table. In this case,
COLUMN_VALUE denotes the reference to the employee objects in the nested table
You can also access the employee number only, for all those employees whose name begins with
Example 6-13 Querying with COLUMN_VALUE
-- Requires Ex. 6-10 and 6-12 SELECT e.COLUMN_VALUE.eno FROM TABLE(SELECT e.empreflist FROM dept_view e WHERE e.dno = 100) e WHERE e.COLUMN_VALUE.ename like 'John%';
To get a tabular output, unnest the list of references by joining the department table with the items in its nested table:
Example 6-14 Querying with COLUMN_VALUE, Unnesting References
-- Requires Ex. 6-10 and 6-12 SELECT d.dno, e.COLUMN_VALUE.eno, e.COLUMN_VALUE.ename FROM dept_view d, TABLE(d.empreflist) e WHERE e.COLUMN_VALUE.ename like 'John%' AND d.dno = 100;
Finally, you can rewrite the preceding query to use the
emp_view instead of the
dept_view to show how to navigate from one view to the other:
Example 6-15 Querying with COLUMN_VALUE, Querying emp_view
-- Requires Ex. 6-10 and 6-12 SELECT e.deptref.dno, DEREF(f.COLUMN_VALUE) FROM emp_view e, TABLE(e.deptref.empreflist) f WHERE e.deptref.dno = 100 AND f.COLUMN_VALUE.ename like 'John%';