Complex views in oracle

Complex Views – Learn how to create complex views , when can we update views and when we cannot update views with example Programs.

Complex view can be constructed on more than one base table.

Following are reference tables to explain complex views.

Creating a Student and Department tables with appropriate columns

Student Table

CREATE TABLE Student
  (
    std_no   NUMBER,
    std_name VARCHAR2(50),
    Marks    NUMBER,
    HOD      VARCHAR2(50),
    dept_no  NUMBER
  );

BEGIN
INSERT INTO Student VALUES(1,'VISWANATH',800,'LOSHMA',30);
INSERT INTO Student VALUES(2,'VIJAY',900,'MADHUSUDHAN',20) ;
INSERT INTO Student VALUES(3,'SHAIK',1000,'VEERENDRA',10); 
INSERT INTO Student VALUES(4,'LEKHAJ',850,'SUDHARMA',40); 
INSERT INTO Student VALUES(5,'ABHIRAM',850,'SUDHARMA',40); 
COMMIT;
END;

Department Table

CREATE TABLE Department
  ( Dept_no NUMBER, Dept_name VARCHAR2(50)
  );

BEGIN
INSERT INTO Department VALUES(10,'IT');
INSERT INTO Department VALUES(20,'ECE');
INSERT INTO Department VALUES(30,'EEE'); 
INSERT INTO Department VALUES(40,'CSE');
COMMIT;
END;

Creating a view with 2 tables

CREATE VIEW std_dept_view_N
  ( std_no,std_name,Dept_name
  ) AS
SELECT std.std_no,
  std.std_name,
  dept.dept_name
FROM Student11 std,
  Department1 dept
WHERE std.dept_no=dept.dept_no;

Select query of view

SELECT * FROM std_dept_view ORDER BY 1 DESC;

STD_NO	STD_NAME	DEPT_NAME
5	ABHIRAM	CSE
5	ABHIRAM	CSE
5	ABHIRAM	CSE
4	LEKHAJ	CSE
4	LEKHAJ	CSE
4	LEKHAJ	CSE
3	SHAIK	IT
3	SHAIK	IT
3	SHAIK	IT
2	VIJAY	ECE
2	VIJAY	ECE
2	VIJAY	ECE
1	VISWANATH	EEE
1	VISWANATH	EEE
1	VISWANATH	EEE

Update of view

UPDATE std_dept_view SET STD_NAME ='SHAIK MOULALI' WHERE STD_NO=3;

Output

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

It raised the exception because the DML statements are not allowed in the complex views for some cases.

If we observe the above output ,we cannot update a view because the base tables doesn’t contain a primary key on any column. Because of not having primary key on the table we cannot find which particular column needs to update on the view.

How to Update Complex Views ?

To update the complex views we have to maintain a primary column on the table.

Following are the key preserving tables

Creating a Student and Department tables with appropriate columns with Primary key columns as std_no and dept_no

Student Table

CREATE TABLE Student
  (
    std_no   NUMBER PRIMARY KEY,
    std_name VARCHAR2(50),
    Marks    NUMBER,
    HOD      VARCHAR2(50),
    dept_no  NUMBER
  );

Department Table

CREATE TABLE Department
  ( Dept_no NUMBER PRIMARY KEY, Dept_name VARCHAR2(50)
  );

Now since we have created the table, we will populate this tables with sample values and Creation of Views for the above tables.

BEGIN
  INSERT INTO Department VALUES
    (10,'IT'
    );
  INSERT INTO Department VALUES
    (20,'ECE'
    );
  INSERT INTO Department VALUES
    (30,'EEE'
    );
  INSERT INTO Department VALUES
    (40,'CSE'
    );
  COMMIT;
END;

BEGIN
  INSERT INTO Student VALUES
    (1,'VISWANATH',800,'LOSHMA',30
    );
  INSERT INTO Student VALUES
    (2,'VIJAY',900,'MADHUSUDHAN',20
    ) ;
  INSERT INTO Student VALUES
    (3,'SHAIK',1000,'VEERENDRA',10
    );
  INSERT INTO Student VALUES
    (4,'LEKHAJ',850,'SUDHARMA',40
    );
  INSERT INTO Student VALUES
    (5,'ABHIRAM',850,'SUDHARMA',40
    );
  COMMIT;
END;

Creating a view for the above created table.

CREATE VIEW std_dept_view
  ( std_no,std_name,Dept_name
  ) AS
SELECT std.std_no,
  std.std_name,
  dept.dept_name
FROM Student std,
  Department dept
WHERE std.dept_no=dept.dept_no;

Select query of view

SELECT * FROM std_dept_view;

STD_NO	STD_NAME	DEPT_NAME
3	SHAIK	IT
2	VIJAY	ECE
1	VISWANATH	EEE
5	ABHIRAM	CSE
4	LEKHAJ	CSE

Update of view

UPDATE std_dept_view  SET STD_NAME ='LEKHAJ SRI KRISHNA'  WHERE STD_NO=4;

Output

1 rows updated.

Select query of view

SELECT * FROM std_dept_view;

STD_NO	STD_NAME	DEPT_NAME
3	SHAIK	IT
2	VIJAY	ECE
1	VISWANATH	EEE
5	ABHIRAM	CSE
4	LEKHAJ SRI KRISHNA	CSE

Update of view

UPDATE std_dept_view  SET STD_NAME ='LEKHAJ SRI KRISHNA'  WHERE dept_name='CSE';

Select query of view

SELECT * FROM std_dept_view;

STD_NO	STD_NAME	DEPT_NAME
3	SHAIK	IT
2	VIJAY	ECE
1	VISWANATH	EEE
5	LEKHAJ SRI KRISHNA	CSE
4	LEKHAJ SRI KRISHNA	CSE

When can we cannot update a view?

The SELECT statement which is used to create the view should not include the following

GROUP BY clause or ORDER BY clause
DISTINCT keyword
NOT NULL values
An aggregate or analytic function
A collection expression in a SELECT list
A sub-query in a SELECT list
A sub-query designated WITH READ ONLY
The view should not be created using nested queries or complex queries.
The view should not be updated when it doesn’t contain a key preserved table.
view cannot update if view contains pseudo columns or expressions.

Here are the few examples for when we cannot update a view.

Creation of view with select with distinct clause

CREATE VIEW std_dept_view1(
std_no,std_name,Dept_name) AS
SELECT DISTINCT std.std_no,std.std_name,dept.dept_name
FROM Student std,Department dept
WHERE std.dept_no=dept.dept_no;

Update of view

UPDATE std_dept_view1 SET STD_NAME ='LEKHAJ SRI KRISHNA' WHERE dept_name='CSE';

Output

SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 - "data manipulation operation not legal on this view"

Creation of view with select with group by and aggregate functions clause

CREATE VIEW std_dept_view1_GRPBY(
cnt_stds,Dept_name) AS
SELECT COUNT(std_name) cnt_stds, Dept_name FROM Student std,Department dept
WHERE std.dept_no=dept.dept_no GROUP BY dept.Dept_name;

Update of view

UPDATE std_dept_view1_GRPBY SET cnt_stds=2 WHERE dept_name='EEE';

Output

SQL Error: ORA-01732: data manipulation operation not legal on this view
01732. 00000 - "data manipulation operation not legal on this view"

When can we update a view?

The view must include the PRIMARY KEY of the table based upon which the view has been created.
it should not have any field made out of aggregate functions
it must not have any DISTINCT clause in its definition
it must not have any GROUP BY or HAVING clause in its definition.
it must not have any SUB-QUERIES in its definitions.
If the view you want to update is based upon another view, the later should be updatable.
Any of the selected output fields (of the view) must not use constants, strings or value expressions.

Student Table

Department Table

Creating a view with 2 tables

How to Update Complex Views ?

Student Table

Department Table

When can we cannot update a view?

When can we update a view?

Related Posts

Parametric Cursor in Oracle PL-SQL

Dynamic Query for PIVOT In Clause using PIVOT XML