alt
Sponsored links
Online Training
Career Series
Exforsys
Exforsys arrow Tutorials arrow Oracle 10g arrow Oracle 10g Tutorials- Updating Views
Site Search


Oracle 10g Tutorials- Updating Views
Article Index
Oracle 10g Tutorials- Updating Views
Page 2
Page 3


.

.

.



INSERT Statements

 

 

 

The following INSERT statement on the emp_dept view succeeds:

INSERT INTO emp_dept (ename, empno, deptno)
VALUES ('KURODA', 9010, 40);

This statement works because only one key-preserved base table is being modified (emp), and 40 is a valid deptno in the dept table (thus satisfying the FOREIGN KEY integrity constraint on the emp table).

An INSERT statement, such as the following, would fail for the same reason that such an UPDATE on the base emp table would fail: the FOREIGN KEY integrity constraint on the emp table is violated (because there is no deptno 77).

INSERT INTO emp_dept (ename, empno, deptno)
VALUES ('KURODA', 9010, 77);

The following INSERT statement would fail with an error (ORA-01776 cannot modify more than one base table through a view):

INSERT INTO emp_dept (empno, ename, loc)
VALUES (9010, 'KURODA', 'BOSTON');

An INSERT cannot implicitly or explicitly refer to columns of a non-key-preserved table. If the join view is defined using the WITH CHECK OPTION clause, then you cannot perform an INSERT to it.

Updating Views that Involve Outer Joins

Views that involve outer joins are modifiable in some cases. For example:

CREATE VIEW Emp_dept_oj1 AS
SELECT Empno, Ename, e.Deptno, Dname, Loc
FROM Emp_tab e, Dept_tab d
WHERE e.Deptno = d.Deptno (+);

The statement:

SELECT * FROM Emp_dept_oj1;

Results in:

EMPNO

ENAME

DEPTNO

DNAME

LOC

7369

SMITH

40

OPERATIONS

BOSTON

7499

ALLEN

30

SALES

CHICAGO

7566

JONES

20

RESEARCH

DALLAS

7654

MARTIN

30

SALES

CHICAGO

7698

BLAKE

30

SALES

CHICAGO

7782

CLARK

10

ACCOUNTING

NEW YORK

7788

SCOTT

20

RESEARCH

DALLAS

7839

KING

10

ACCOUNTING

NEW YORK

7844

TURNER

30

SALES

CHICAGO

7876

ADAMS

20

RESEARCH

DALLAS

7900

JAMES

30

SALES

CHICAGO

7902

FORD

20

RESEARCH

DALLAS

7934

MILLER

10

ACCOUNTING

NEW YORK

7521

WARD

30

SALES

CHICAGO

14 rows selected.

Columns in the base emp_tab table of emp_dept_oj1 are modifiable through the view, because emp_tab is a key-preserved table in the join.

The following view also contains an outer join:

CREATE VIEW Emp_dept_oj2 AS
SELECT e.Empno, e.Ename, e.Deptno, d.Dname, d.Loc
FROM Emp_tab e, Dept_tab d
WHERE e.Deptno (+) = d.Deptno;

The statement:

SELECT * FROM Emp_dept_oj2;

Results in:

EMPNO

ENAME

DEPTNO

DNAME

LOC

7782

CLARK

10

ACCOUNTING

NEW YORK

7839

KING

10

ACCOUNTING

NEW YORK

7934

MILLER

10

ACCOUNTING

NEW YORK

7369

SMITH

20

RESEARCH

DALLAS

7876

ADAMS

20

RESEARCH

DALLAS

7902

FORD

20

RESEARCH

DALLAS

7788

SCOTT

20

RESEARCH

DALLAS

7566

JONES

20

RESEARCH

DALLAS

7499

ALLEN

30

SALES

CHICAGO

7698

BLAKE

30

SALES

CHICAGO

7654

MARTIN

30

SALES

CHICAGO

7900

JAMES

30

SALES

CHICAGO

7844

TURNER

30

SALES

CHICAGO

7521

WARD

30

SALES

CHICAGO




OPERATIONS

BOSTON

15 rows selected.

In this view, emp_tab is no longer a key-preserved table, because the empno column in the result of the join can have nulls (the last row in the preceding SELECT statement). So, UPDATE, DELETE, and INSERT operations cannot be performed on this view.

In the case of views containing an outer join on other nested views, a table is key preserved if the view or views containing the table are merged into their outer views, all the way to the top. A view which is being outer-joined is currently merged only if it is "simple." For example:

SELECT Col1, Col2, ... FROM T;

The select list of the view has no expressions, and there is no WHERE clause.

Consider the following set of views:

CREATE VIEW Emp_v AS
SELECT Empno, Ename, Deptno
FROM Emp_tab;

CREATE VIEW Emp_dept_oj1 AS
SELECT e.*, Loc, d.Dname
FROM Emp_v e, Dept_tab d
WHERE e.Deptno = d.Deptno (+);

In these examples, emp_v is merged into emp_dept_oj1 because emp_v is a simple view, and so emp_tab is a key-preserved table. But if emp_v is changed as follows:

CREATE VIEW Emp_v_2 AS
SELECT Empno, Ename, Deptno
FROM Emp_tab
WHERE Sal > 1000;

Then, because of the presence of the WHERE clause, emp_v_2 cannot be merged into emp_dept_oj1, and hence emp_tab is no longer a key-preserved table.

If you are in doubt whether a view is modifiable, then you can SELECT from the view USER_UPDATABLE_COLUMNS to see if it is. For example

SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = 'EMP_DEPT_VIEW';

This might return:

OWNER
TABLE_NAME
COLUMN_NAM
UPD
SCOTT
EMP_DEPT_V
EMPNO
NO
SCOTT
EMP_DEPT_V
ENAME
NO
SCOTT
EMP_DEPT_V
DEPTNO
NO
SCOTT
EMP_DEPT_V
DNAME
NO
SCOTT
EMP_DEPT_V
LOC
NO

5 rows selected.


Trackback(0)
Comments (0)add comment

Write comment

busy

 
< Prev   Next >
Exforsys Offers
© 2008 Exforsys.com
Joomla! is Free Software released under the GNU/GPL License.
Page copy protected against web site content infringement by Copyscape