Quick Introduction to Oracle Views.

What is a View?

You can present logical subsets or combinations of data by creating views of tables. A view is a logical table based on a table or another view. A view contains no data of its own, but is like a window through which data from tables can be viewed or changed.

Advantages of Views

• Views restrict access to the data because it displays selected columns from the table.
• Views can be used to make simple queries to retrieve the results of complicated queries. For example, views can be used to query information from multiple tables without the user knowing how to write a join statement.
• Views provide data independence for ad hoc users and application programs. One view can be used to retrieve data from several tables.
• Views provide groups of users access to data according to their particular criteria.

Rules for Performing DML Operations on a View

• You cannot remove a row if the view contains the following:
• Group functions
• A GROUP BY clause
• The DISTINCT keyword
• The pseudocolumn ROWNUM keyword

• You cannot modify data in a view if it contains:
• Group functions
• A GROUP BY clause
• The DISTINCT keyword
• The pseudocolumn ROWNUM keyword
• Columns defined by expressions

• You cannot add data through a view if the view includes:
• Group functions
• A GROUP BY clause
• The DISTINCT keyword
• The pseudocolumn ROWNUM keyword
• Columns defined by expressions
• NOT NULL columns in the base tables that are not selected by the view

Example:1

Create a simple view.

CREATE TABLE emp (
employee_id INTEGER
CONSTRAINT employees_pk PRIMARY KEY,
manager_id INTEGER,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(10) NOT NULL,
title VARCHAR2(20),
salary NUMBER(6, 0));

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
1, NULL, 'James', 'Smith', 'CEO', 800000);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
2, 1, 'Ron', 'Johnson', 'Sales Manager', 600000);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
3, 2, 'Fred', 'Hobbs', 'Salesperson', 150000);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
4, 2, 'Susan', 'Jones', 'Salesperson', 500000);

INSERT INTO emp (
employee_id, manager_id, first_name, last_name, title, salary
) VALUES (
5, 3, 'Ran', 'Jill', 'Salesperson', 200000);

-- commit the transaction
COMMIT;

CREATE OR REPLACE VIEW tmp_view1 AS
SELECT employee_id, manager_id, first_name, salary
FROM emp;

SQL> describe tmp_view1;
Name Type Nullable Default Comments
----------- ------------ -------- ------- --------
EMPLOYEE_ID INTEGER
MANAGER_ID INTEGER Y
FIRST_NAME VARCHAR2(10)
SALARY NUMBER(6) Y

Example: 2

Create a view with Group Function and try to execute DML operations to that view.

CREATE OR REPLACE VIEW tmp_view2 AS
SELECT MAX(salary)AS Maximum_Salary
FROM emp;

SQL> DELETE tmp_view2;

DELETE tmp_view2

ORA-01732: data manipulation operation not legal on this view

SQL> UPDATE tmp_view2
2 SET Maximum_Salary = 912000;

UPDATE tmp_view2
SET Maximum_Salary = 912000

ORA-01732: data manipulation operation not legal on this view

SQL> INSERT INTO tmp_view2
2 VALUES(988000);

INSERT INTO tmp_view2
VALUES(988000)

ORA-01732: data manipulation operation not legal on this view

Example: 3

Create a view with Group By and try to execute DML operations to that view.

CREATE OR REPLACE VIEW tmp_view3 AS
SELECT manager_id, first_name, salary
FROM emp
GROUP BY manager_id, first_name, salary;

SQL> DELETE tmp_view3
2 WHERE manager_id = 2;

DELETE tmp_view3
WHERE manager_id = 2

ORA-01732: data manipulation operation not legal on this view

SQL> UPDATE tmp_view3
2 SET salary= salary + (0.1*salary);

UPDATE tmp_view3
SET salary= salary + (0.1*salary)

ORA-01732: data manipulation operation not legal on this view

SQL> INSERT INTO tmp_view3
2 VALUES(8, 'Will Smith', 205444);

INSERT INTO tmp_view3
VALUES(8, 'Will Smith', 205444)

ORA-01400: cannot insert NULL into ("IFSAPP"."EMP"."EMPLOYEE_ID")

Example: 4

Create a view with DISTINCT and try to execute DML operations to that view.

CREATE OR REPLACE VIEW tmp_view4 AS
SELECT DISTINCT(manager_id)
FROM emp;

SQL> DELETE tmp_view4
2 WHERE manager_id = 2;

DELETE tmp_view4
WHERE manager_id = 2

ORA-01732: data manipulation operation not legal on this view

SQL> UPDATE tmp_view4
2 SET manager_id= 4
3 WHERE manager_id IS NULL;

UPDATE tmp_view4
SET manager_id= 4
WHERE manager_id IS NULL

ORA-01732: data manipulation operation not legal on this view

SQL> INSERT INTO tmp_view4
2 VALUES(9);

INSERT INTO tmp_view4
VALUES(9)

ORA-01732: data manipulation operation not legal on this view

Example 5:

Create a view with rownum pseudocolumn and try to execute DML operations to that view.

CREATE OR REPLACE VIEW tmp_view5 AS
SELECT employee_id, first_name
FROM emp
WHERE rownum < 3;

SQL> DELETE tmp_view5
2 WHERE employee_id = 2;

DELETE tmp_view5
WHERE employee_id = 2

ORA-01732: data manipulation operation not legal on this view

SQL> UPDATE tmp_view5
2 SET first_name = 'Butner'
3 WHERE employee_id = 2;

UPDATE tmp_view5
SET first_name = 'Butner'
WHERE employee_id = 2

ORA-01732: data manipulation operation not legal on this view

SQL> INSERT INTO tmp_view5
2 VALUES(11, 'Adam');

INSERT INTO tmp_view5
VALUES(11, 'Adam')

ORA-01732: data manipulation operation not legal on this view