VIEW
Definition:
A view is a representation of a SQL statement that is stored in memory so that it can be re-used. A view
is a logical entity or logical table that is essentially a SQL statement stored in the database in the system
tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace.
You may also want to visit the Oracle CREATE_VIEW Statement page for additional syntax and examples
on views.
Example Syntax:
CREATE OR REPLACE VIEW <view_name> AS
SELECT <column_name>
FROM <table_name>
WHERE <predicates>;
A view is simply the representation of a SQL statement that is stored in memory so that it can
easily be re-used. For example, if we frequently issue the following query
SELECT empid FROM emp;
I might well want to make this a view (the reality is that we would probably never create a view
for a statement this simple but we wanted to use an easy example).
To create a view use the create view command as seen in this example
CREATE VIEW
view_emp
AS
SELECT empid FROM emp;
This command creates a new view called VIEW_EMP. Note that this command does not result
in anything being actually stored in the database at all except for a data dictionary entry that
defines this view. This means that every time you query this view, Oracle has to go out and
execute the view and query the database data. We can query the view like this:
SELECT * FROM view_emp WHERE empid BETWEEN 500 AND 1000;
And Oracle will transform the query into this:
SELECT * FROM (select empid from emp) WHERE empid BETWEEN 500
AND 1000;
Create Oracle Views tips
Oracle Views
As a DBA one of the types of objects you will manage will be views. In this section we will
introduce you to Oracle view constructs. Following your introduction to views, we will
discuss the benefits and downsides of views in Oracle.
Inside Oracle Views
A view is simply the representation of a SQL statement that is stored in memory so that it
can easily be re-used. For example, if we frequently issue the following query
SELECT empid FROM emp;
I might well want to make this a view (the reality is that we would probably never create a
view for a statement this simple but we wanted to use an easy example).
To create a view use the create view command as seen in this example
CREATE VIEW
view_emp
AS
SELECT empid FROM emp;
This command creates a new view called VIEW_EMP. Note that this command does not
result in anything being actually stored in the database at all except for a data dictionary
entry that defines this view. This means that every time you query this view, Oracle has to
go out and execute the view and query the database data. We can query the view like this:
SELECT * FROM view_emp WHERE empid BETWEEN 500 AND 1000;
And Oracle will transform the query into this:
SELECT * FROM (select empid from emp) WHERE empid BETWEEN 500
AND 1000;
Benefits of Oracle Views
Oracle views offer some compelling benefits. These include:
* Commonality of code being used. Since a view is based on one common set of SQL, this
means that when it is called it’s less likely to require parsing. This is because the basic
underlying SQL that is called is always the same. However, since you can add additional
where clauses when calling a view, you still need to use bind variables. Additional where
clauses without a bind variable can still cause a hard parse!
* Security. Views have long been used to hide the tables that actually contain the data you
are querying. Also, views can be used to restrict the columns that a given user has access to.
Using views for security on less complex databases is probably not a bad thing. As
databases become more complex, this solution becomes harder to scale and other solutions
will be needed.
* Predicate pushing. Oracle supports pushing of predicates into a given view. Assume we
had a set of layered views, like this:
Views are known as logical tables. They represent the data of one of more tables. A view derives
its data from the tables on which it is based. These tables are called base tables. Views can be
based on actual tables or another view also.
Whatever DML operations you performed on a view they actually affect the base table of the
view. You can treat views same as any other table. You can Query, Insert, Update and delete
from views, just as any other table.
Views are very powerful and handy since they can be treated just like any other table but do not
occupy the space of a table.
The following sections explain how to create, replace, and drop views using SQL commands.
Creating Views
Suppose we have EMP and DEPT table. To see the empno, ename, sal, deptno, department name
and location we have to give a join query like this.
select e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc
From emp e, dept d where e.deptno=d.deptno;
So everytime we want to see emp details and department names where they are working we have
to give a long join query. Instead of giving this join query again and again, we can create a view
on these table by using a CREATE VIEW command given below
create view emp_det as select e.empno,
e.ename,e.sal,e.deptno,d.dname,d.loc
from emp e, dept d where e.deptno=d.deptno;
Now to see the employee details and department names we don’t have to give a join query, we
can just type the following simple query.
select * from emp_det;
This will show same result as you have type the long join query. Now you can treat this
EMP_DET view same as any other table.
For example, suppose all the employee working in Department No. 10 belongs to accounts
department and most of the time you deal with these people. So every time you have to give a
DML or Select statement you have to give a WHERE condition like .....WHERE
DEPTNO=10. To avoid this, you can create a view as given below
CREATE VIEW accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 10
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Now to see the account people you don’t have to give a query with where condition you can just
type the following query.
Select * from accounts_staff;
Select sum(sal) from accounst_staff;
Select max(sal) from accounts_staff;
As you can see how views make things easier.
The query that defines the ACCOUNTS_STAFF view references only rows in department 10.
Furthermore, WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATE
statements issued against the view are not allowed to create or result in rows that the view cannot
select.
Considering the example above, the following INSERT statement successfully inserts a row into
the EMP table through the ACCOUNTS_STAFF view:
INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10);
However, the following INSERT statement is rolled back and returns an error because it attempts
to insert a row for department number 30, which could not be selected using the
ACCOUNTS_STAFF view:
INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30);
Creating FORCE VIEWS
A view can be created even if the defining query of the view cannot be executed, as long as the
CREATE VIEW command has no syntax errors. We call such a view a view with errors. For
example, if a view refers to a non-existent table or an invalid column of an existing table, or if
the owner of the view does not have the required privileges, then the view can still be created
and entered into the data dictionary.
You can only create a view with errors by using the FORCE option of the CREATE VIEW command:
CREATE FORCE VIEW AS ...;
When a view is created with errors, Oracle returns a message and leaves the status of the view as
INVALID. If conditions later change so that the query of an invalid view can be executed, then the
view can be recompiled and become valid. Oracle dynamically compiles the invalid view if you
attempt to use it.
Replacing/Altering Views
To alter the definition of a view, you must replace the view using one of the following methods:
A view can be dropped and then re-created. When a view is dropped, all grants of
corresponding view privileges are revoked from roles and users. After the view is recreated,
necessary privileges must be regranted.
A view can be replaced by redefining it with a CREATE VIEW statement that contains the
OR REPLACE option. This option replaces the current definition of a view, but preserves
the present security authorizations.
For example, assume that you create the ACCOUNTS_STAFF view, as given in a previous example.
You also grant several object privileges to roles and other users. However, now you realize that
you must redefine the ACCOUNTS_STAFF view to correct the department number specified in the
WHERE clause of the defining query, because it should have been 30. To preserve the grants of
object privileges that you have made, you can replace the current version of the
ACCOUNTS_STAFF view with the following statement:
CREATE OR REPLACE VIEW Accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 30
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Replacing a view has the following effects:
Replacing a view replaces the view's definition in the data dictionary. All underlying
objects referenced by the view are not affected.
If previously defined but not included in the new view definition, then the constraint
associated with the WITH CHECK OPTION for a view's definition is dropped.
All views and PL/SQL program units dependent on a replaced view become invalid.
With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a
view. The following statement inserts a new row into the EMP table using the ACCOUNTS_STAFF
view:
INSERT INTO Accounts_staff
VALUES (199, 'ABID', 30);
Restrictions on DML operations for views use the following criteria in the order listed:
1. If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY
clause, or a group function, then rows cannot be inserted into, updated in, or deleted from
the base tables using the view.
2. If a view is defined with WITH CHECK OPTION, then a row cannot be inserted into, or
updated in, the base table (using the view), if the view cannot select the row from the
base table.
3. If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, then
a row cannot be inserted into the base table using the view.
4. If the view was created by using an expression, such as DECODE(deptno, 10, "SALES", ...),
then rows cannot be inserted into or updated in the base table using the view.
The constraint created by WITH CHECK OPTION of the ACCOUNTS_STAFF view only allows rows
that have a department number of 10 to be inserted into, or updated in, the EMP table.
Alternatively, assume that the ACCOUNTS_STAFF view is defined by the following statement (that
is, excluding the DEPTNO column):
CREATE VIEW Accounts_staff AS
SELECT Empno, Ename
FROM Emp
WHERE Deptno = 10
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Considering this view definition, you can update the EMPNO or ENAME fields of existing records,
but you cannot insert rows into the EMP table through the ACCOUNTS_STAFF view because the
view does not let you alter the DEPTNO field. If you had defined a DEFAULT value of 10 on the
DEPTNO field, then you could perform inserts.
If you don’t want any DML operations to be performed on views, create them WITH READ
ONLY option. Then no DML operations are allowed on views.
Referencing Invalid Views
When a user attempts to reference an invalid view, Oracle returns an error message to the user:
ORA-04063: view 'view_name' has errors
This error message is returned when a view exists but is unusable due to errors in its query
(whether it had errors when originally created or it was created successfully but became unusable
later because underlying objects were altered or dropped).
Dropping Views
Use the SQL command DROP VIEW to drop a view. For example:
DROP VIEW Accounts_staff;
Modifying a Join View
Oracle allows you, with some restrictions, to modify views that involve joins. Consider the
following simple view:
CREATE VIEW Emp_view AS
SELECT Ename, Empno, deptno FROM Emp;
This view does not involve a join operation. If you issue the SQL statement:
UPDATE Emp_view SET Ename = 'SHAHRYAR' WHERE Empno = 109;
then the EMP base table that underlies the view changes, and employee 109's name changes from
ASHI to SHAHRYAR in the EMP table.
However, if you create a view that involves a join operation, such as:
CREATE VIEW Emp_dept_view AS
SELECT e.Empno, e.Ename, e.Deptno, e.Sal, d.Dname, d.Loc
FROM Emp e, Dept d /* JOIN operation */
WHERE e.Deptno = d.Deptno
AND d.Loc IN ('HYD', 'BOM', 'DEL');
then there are restrictions on modifying either the EMP or the DEPT base table through this view.
A modifiable join view is a view that contains more than one table in the top-level FROM clause
of the SELECT statement, and that does not contain any of the following:
DISTINCT operator
Aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE
Set operations: UNION, UNION ALL, INTERSECT, MINUS
GROUP BY or HAVING clauses
START WITH or CONNECT BY clauses
ROWNUM pseudocolumn
Any UPDATE, INSERT, or DELETE statement on a join view can modify only one underlying base
table.
The following example shows an UPDATE statement that successfully modifies the
EMP_DEPT_VIEW view:
UPDATE Emp_dept_view
SET Sal = Sal * 1.10
WHERE Deptno = 10;
The following UPDATE statement would be disallowed on the EMP_DEPT_VIEW view:
UPDATE Emp_dept_view
SET Loc = 'BOM'
WHERE Ename = 'SAMI';
This statement fails with an ORA-01779 error ("cannot modify a column which maps to a non
key-preserved table"), because it attempts to modify the underlying DEPT table, and the DEPT
table is not key preserved in the EMP_DEPT view.
In general, all modifiable columns of a join view must map to columns of a key-preserved table.
If the view is defined using the WITH CHECK OPTION clause, then all join columns and all columns
of repeated tables are not modifiable.
So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION, then the
following UPDATE statement would fail:
UPDATE Emp_dept_view
SET Deptno = 10
WHERE Ename = 'SAMI';
The statement fails because it is trying to update a join column.
Deleting from a Join View
You can delete from a join view provided there is one and only one key-preserved table in the
join.
The following DELETE statement works on the EMP_DEPT view:
DELETE FROM Emp_dept_view
WHERE Ename = 'SMITH';
This DELETE statement on the EMP_DEPT view is legal because it can be translated to a DELETE
operation on the base EMP table, and because the EMP table is the only key-preserved table in the
join.
In the following view, a DELETE operation cannot be performed on the view because both E1 and
E2 are key-preserved tables:
CREATE VIEW emp_emp AS
SELECT e1.Ename, e2.Empno, e1.Deptno
FROM Emp e1, Emp e2
WHERE e1.Empno = e2.Empno;
If a view is defined using the WITH CHECK OPTION clause and the key-preserved table is repeated,
then rows cannot be deleted from such a view. For example:
CREATE VIEW Emp_mgr AS
SELECT e1.Ename, e2.Ename Mname
FROM Emp e1, Emp e2
WHERE e1.mgr = e2.Empno
WITH CHECK OPTION;
No deletion can be performed on this view because the view involves a self-join of the table that
is key preserved.
Inserting into a Join View
The following INSERT statement on the EMP_DEPT view succeeds, because only one keypreserved
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).
INSERT INTO Emp_dept (Ename, Empno, Deptno)
VALUES ('ASHU', 119, 40);
The following INSERT statement fails for the same reason: This UPDATE on the base EMP table
would fail: the FOREIGN KEY integrity constraint on the EMP table is violated.
INSERT INTO Emp_dept (Ename, Empno, Deptno)
VALUES ('ASHU', 110, 77);
The following INSERT statement fails with an ORA-01776 error ("cannot modify more than one
base table through a view").
INSERT INTO Emp_dept (Ename, Empno, Deptno)
VALUES (110, 'TANNU’, 'BOMBAY');
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.
Listing Information about VIEWS.
To see how many views are there in your schema. Give the following query.
Select * from user_views;
To see which columns are updatable in join views.
USER_UPDATABLE_COLUMNS Shows all columns in all tables and views in the user's schema that
are modifiable
DBA_UPDATABLE_COLUMNS Shows all columns in all tables and views in the DBA schema that
are modifiable
ALL_UPDATABLE_VIEWS Shows all columns in all tables and views that are modifiable
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 EMPNO NO
SCOTT EMP_DEPT ENAME NO
SCOTT EMP_DEPT DEPTNO NO
SCOTT EMP_DEPT DNAME NO
SCOTT EMP_DEPT LOC NO
5 rows selected.
Definition:
A view is a representation of a SQL statement that is stored in memory so that it can be re-used. A view
is a logical entity or logical table that is essentially a SQL statement stored in the database in the system
tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace.
You may also want to visit the Oracle CREATE_VIEW Statement page for additional syntax and examples
on views.
Example Syntax:
CREATE OR REPLACE VIEW <view_name> AS
SELECT <column_name>
FROM <table_name>
WHERE <predicates>;
A view is simply the representation of a SQL statement that is stored in memory so that it can
easily be re-used. For example, if we frequently issue the following query
SELECT empid FROM emp;
I might well want to make this a view (the reality is that we would probably never create a view
for a statement this simple but we wanted to use an easy example).
To create a view use the create view command as seen in this example
CREATE VIEW
view_emp
AS
SELECT empid FROM emp;
This command creates a new view called VIEW_EMP. Note that this command does not result
in anything being actually stored in the database at all except for a data dictionary entry that
defines this view. This means that every time you query this view, Oracle has to go out and
execute the view and query the database data. We can query the view like this:
SELECT * FROM view_emp WHERE empid BETWEEN 500 AND 1000;
And Oracle will transform the query into this:
SELECT * FROM (select empid from emp) WHERE empid BETWEEN 500
AND 1000;
Create Oracle Views tips
Oracle Views
As a DBA one of the types of objects you will manage will be views. In this section we will
introduce you to Oracle view constructs. Following your introduction to views, we will
discuss the benefits and downsides of views in Oracle.
Inside Oracle Views
A view is simply the representation of a SQL statement that is stored in memory so that it
can easily be re-used. For example, if we frequently issue the following query
SELECT empid FROM emp;
I might well want to make this a view (the reality is that we would probably never create a
view for a statement this simple but we wanted to use an easy example).
To create a view use the create view command as seen in this example
CREATE VIEW
view_emp
AS
SELECT empid FROM emp;
This command creates a new view called VIEW_EMP. Note that this command does not
result in anything being actually stored in the database at all except for a data dictionary
entry that defines this view. This means that every time you query this view, Oracle has to
go out and execute the view and query the database data. We can query the view like this:
SELECT * FROM view_emp WHERE empid BETWEEN 500 AND 1000;
And Oracle will transform the query into this:
SELECT * FROM (select empid from emp) WHERE empid BETWEEN 500
AND 1000;
Benefits of Oracle Views
Oracle views offer some compelling benefits. These include:
* Commonality of code being used. Since a view is based on one common set of SQL, this
means that when it is called it’s less likely to require parsing. This is because the basic
underlying SQL that is called is always the same. However, since you can add additional
where clauses when calling a view, you still need to use bind variables. Additional where
clauses without a bind variable can still cause a hard parse!
* Security. Views have long been used to hide the tables that actually contain the data you
are querying. Also, views can be used to restrict the columns that a given user has access to.
Using views for security on less complex databases is probably not a bad thing. As
databases become more complex, this solution becomes harder to scale and other solutions
will be needed.
* Predicate pushing. Oracle supports pushing of predicates into a given view. Assume we
had a set of layered views, like this:
Views are known as logical tables. They represent the data of one of more tables. A view derives
its data from the tables on which it is based. These tables are called base tables. Views can be
based on actual tables or another view also.
Whatever DML operations you performed on a view they actually affect the base table of the
view. You can treat views same as any other table. You can Query, Insert, Update and delete
from views, just as any other table.
Views are very powerful and handy since they can be treated just like any other table but do not
occupy the space of a table.
The following sections explain how to create, replace, and drop views using SQL commands.
Creating Views
Suppose we have EMP and DEPT table. To see the empno, ename, sal, deptno, department name
and location we have to give a join query like this.
select e.empno,e.ename,e.sal,e.deptno,d.dname,d.loc
From emp e, dept d where e.deptno=d.deptno;
So everytime we want to see emp details and department names where they are working we have
to give a long join query. Instead of giving this join query again and again, we can create a view
on these table by using a CREATE VIEW command given below
create view emp_det as select e.empno,
e.ename,e.sal,e.deptno,d.dname,d.loc
from emp e, dept d where e.deptno=d.deptno;
Now to see the employee details and department names we don’t have to give a join query, we
can just type the following simple query.
select * from emp_det;
This will show same result as you have type the long join query. Now you can treat this
EMP_DET view same as any other table.
For example, suppose all the employee working in Department No. 10 belongs to accounts
department and most of the time you deal with these people. So every time you have to give a
DML or Select statement you have to give a WHERE condition like .....WHERE
DEPTNO=10. To avoid this, you can create a view as given below
CREATE VIEW accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 10
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Now to see the account people you don’t have to give a query with where condition you can just
type the following query.
Select * from accounts_staff;
Select sum(sal) from accounst_staff;
Select max(sal) from accounts_staff;
As you can see how views make things easier.
The query that defines the ACCOUNTS_STAFF view references only rows in department 10.
Furthermore, WITH CHECK OPTION creates the view with the constraint that INSERT and UPDATE
statements issued against the view are not allowed to create or result in rows that the view cannot
select.
Considering the example above, the following INSERT statement successfully inserts a row into
the EMP table through the ACCOUNTS_STAFF view:
INSERT INTO Accounts_staff VALUES (110, 'ASHI', 10);
However, the following INSERT statement is rolled back and returns an error because it attempts
to insert a row for department number 30, which could not be selected using the
ACCOUNTS_STAFF view:
INSERT INTO Accounts_staff VALUES (111, 'SAMI', 30);
Creating FORCE VIEWS
A view can be created even if the defining query of the view cannot be executed, as long as the
CREATE VIEW command has no syntax errors. We call such a view a view with errors. For
example, if a view refers to a non-existent table or an invalid column of an existing table, or if
the owner of the view does not have the required privileges, then the view can still be created
and entered into the data dictionary.
You can only create a view with errors by using the FORCE option of the CREATE VIEW command:
CREATE FORCE VIEW AS ...;
When a view is created with errors, Oracle returns a message and leaves the status of the view as
INVALID. If conditions later change so that the query of an invalid view can be executed, then the
view can be recompiled and become valid. Oracle dynamically compiles the invalid view if you
attempt to use it.
Replacing/Altering Views
To alter the definition of a view, you must replace the view using one of the following methods:
A view can be dropped and then re-created. When a view is dropped, all grants of
corresponding view privileges are revoked from roles and users. After the view is recreated,
necessary privileges must be regranted.
A view can be replaced by redefining it with a CREATE VIEW statement that contains the
OR REPLACE option. This option replaces the current definition of a view, but preserves
the present security authorizations.
For example, assume that you create the ACCOUNTS_STAFF view, as given in a previous example.
You also grant several object privileges to roles and other users. However, now you realize that
you must redefine the ACCOUNTS_STAFF view to correct the department number specified in the
WHERE clause of the defining query, because it should have been 30. To preserve the grants of
object privileges that you have made, you can replace the current version of the
ACCOUNTS_STAFF view with the following statement:
CREATE OR REPLACE VIEW Accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 30
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Replacing a view has the following effects:
Replacing a view replaces the view's definition in the data dictionary. All underlying
objects referenced by the view are not affected.
If previously defined but not included in the new view definition, then the constraint
associated with the WITH CHECK OPTION for a view's definition is dropped.
All views and PL/SQL program units dependent on a replaced view become invalid.
With some restrictions, rows can be inserted into, updated in, or deleted from a base table using a
view. The following statement inserts a new row into the EMP table using the ACCOUNTS_STAFF
view:
INSERT INTO Accounts_staff
VALUES (199, 'ABID', 30);
Restrictions on DML operations for views use the following criteria in the order listed:
1. If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY
clause, or a group function, then rows cannot be inserted into, updated in, or deleted from
the base tables using the view.
2. If a view is defined with WITH CHECK OPTION, then a row cannot be inserted into, or
updated in, the base table (using the view), if the view cannot select the row from the
base table.
3. If a NOT NULL column that does not have a DEFAULT clause is omitted from the view, then
a row cannot be inserted into the base table using the view.
4. If the view was created by using an expression, such as DECODE(deptno, 10, "SALES", ...),
then rows cannot be inserted into or updated in the base table using the view.
The constraint created by WITH CHECK OPTION of the ACCOUNTS_STAFF view only allows rows
that have a department number of 10 to be inserted into, or updated in, the EMP table.
Alternatively, assume that the ACCOUNTS_STAFF view is defined by the following statement (that
is, excluding the DEPTNO column):
CREATE VIEW Accounts_staff AS
SELECT Empno, Ename
FROM Emp
WHERE Deptno = 10
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;
Considering this view definition, you can update the EMPNO or ENAME fields of existing records,
but you cannot insert rows into the EMP table through the ACCOUNTS_STAFF view because the
view does not let you alter the DEPTNO field. If you had defined a DEFAULT value of 10 on the
DEPTNO field, then you could perform inserts.
If you don’t want any DML operations to be performed on views, create them WITH READ
ONLY option. Then no DML operations are allowed on views.
Referencing Invalid Views
When a user attempts to reference an invalid view, Oracle returns an error message to the user:
ORA-04063: view 'view_name' has errors
This error message is returned when a view exists but is unusable due to errors in its query
(whether it had errors when originally created or it was created successfully but became unusable
later because underlying objects were altered or dropped).
Dropping Views
Use the SQL command DROP VIEW to drop a view. For example:
DROP VIEW Accounts_staff;
Modifying a Join View
Oracle allows you, with some restrictions, to modify views that involve joins. Consider the
following simple view:
CREATE VIEW Emp_view AS
SELECT Ename, Empno, deptno FROM Emp;
This view does not involve a join operation. If you issue the SQL statement:
UPDATE Emp_view SET Ename = 'SHAHRYAR' WHERE Empno = 109;
then the EMP base table that underlies the view changes, and employee 109's name changes from
ASHI to SHAHRYAR in the EMP table.
However, if you create a view that involves a join operation, such as:
CREATE VIEW Emp_dept_view AS
SELECT e.Empno, e.Ename, e.Deptno, e.Sal, d.Dname, d.Loc
FROM Emp e, Dept d /* JOIN operation */
WHERE e.Deptno = d.Deptno
AND d.Loc IN ('HYD', 'BOM', 'DEL');
then there are restrictions on modifying either the EMP or the DEPT base table through this view.
A modifiable join view is a view that contains more than one table in the top-level FROM clause
of the SELECT statement, and that does not contain any of the following:
DISTINCT operator
Aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE
Set operations: UNION, UNION ALL, INTERSECT, MINUS
GROUP BY or HAVING clauses
START WITH or CONNECT BY clauses
ROWNUM pseudocolumn
Any UPDATE, INSERT, or DELETE statement on a join view can modify only one underlying base
table.
The following example shows an UPDATE statement that successfully modifies the
EMP_DEPT_VIEW view:
UPDATE Emp_dept_view
SET Sal = Sal * 1.10
WHERE Deptno = 10;
The following UPDATE statement would be disallowed on the EMP_DEPT_VIEW view:
UPDATE Emp_dept_view
SET Loc = 'BOM'
WHERE Ename = 'SAMI';
This statement fails with an ORA-01779 error ("cannot modify a column which maps to a non
key-preserved table"), because it attempts to modify the underlying DEPT table, and the DEPT
table is not key preserved in the EMP_DEPT view.
In general, all modifiable columns of a join view must map to columns of a key-preserved table.
If the view is defined using the WITH CHECK OPTION clause, then all join columns and all columns
of repeated tables are not modifiable.
So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION, then the
following UPDATE statement would fail:
UPDATE Emp_dept_view
SET Deptno = 10
WHERE Ename = 'SAMI';
The statement fails because it is trying to update a join column.
Deleting from a Join View
You can delete from a join view provided there is one and only one key-preserved table in the
join.
The following DELETE statement works on the EMP_DEPT view:
DELETE FROM Emp_dept_view
WHERE Ename = 'SMITH';
This DELETE statement on the EMP_DEPT view is legal because it can be translated to a DELETE
operation on the base EMP table, and because the EMP table is the only key-preserved table in the
join.
In the following view, a DELETE operation cannot be performed on the view because both E1 and
E2 are key-preserved tables:
CREATE VIEW emp_emp AS
SELECT e1.Ename, e2.Empno, e1.Deptno
FROM Emp e1, Emp e2
WHERE e1.Empno = e2.Empno;
If a view is defined using the WITH CHECK OPTION clause and the key-preserved table is repeated,
then rows cannot be deleted from such a view. For example:
CREATE VIEW Emp_mgr AS
SELECT e1.Ename, e2.Ename Mname
FROM Emp e1, Emp e2
WHERE e1.mgr = e2.Empno
WITH CHECK OPTION;
No deletion can be performed on this view because the view involves a self-join of the table that
is key preserved.
Inserting into a Join View
The following INSERT statement on the EMP_DEPT view succeeds, because only one keypreserved
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).
INSERT INTO Emp_dept (Ename, Empno, Deptno)
VALUES ('ASHU', 119, 40);
The following INSERT statement fails for the same reason: This UPDATE on the base EMP table
would fail: the FOREIGN KEY integrity constraint on the EMP table is violated.
INSERT INTO Emp_dept (Ename, Empno, Deptno)
VALUES ('ASHU', 110, 77);
The following INSERT statement fails with an ORA-01776 error ("cannot modify more than one
base table through a view").
INSERT INTO Emp_dept (Ename, Empno, Deptno)
VALUES (110, 'TANNU’, 'BOMBAY');
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.
Listing Information about VIEWS.
To see how many views are there in your schema. Give the following query.
Select * from user_views;
To see which columns are updatable in join views.
USER_UPDATABLE_COLUMNS Shows all columns in all tables and views in the user's schema that
are modifiable
DBA_UPDATABLE_COLUMNS Shows all columns in all tables and views in the DBA schema that
are modifiable
ALL_UPDATABLE_VIEWS Shows all columns in all tables and views that are modifiable
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 EMPNO NO
SCOTT EMP_DEPT ENAME NO
SCOTT EMP_DEPT DEPTNO NO
SCOTT EMP_DEPT DNAME NO
SCOTT EMP_DEPT LOC NO
5 rows selected.
No comments:
Post a Comment