top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Oracle: I want to use a view to update a table. Is it possible?

+1 vote
Oracle: I want to use a view to update a table. Is it possible?
posted Feb 9, 2015 by Suchithra

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

2 Answers

+2 votes
Best answer

Yes and No. A table can be updated through a view only if it matches certain
1. Simple Sub Set of Data
2. All Not Null Columns are included
3. Based on a single table
So, you can update a view that is like this
create view v_emp as select * from emp;

but you cannot update a view like
create view v_emp as select emp_id, sum(sal)
from emp
group by emp_id;

answer Feb 10, 2015 by Arun Gowda
simple to understand
0 votes

Yes, it is possible but It can be tricky, and usually is not advisable.

An updatable view is one you can use to insert, update, or delete base table rows. You can create a view to be inherently updatable, or you can create an INSTEAD OF trigger on any view to make it updatable.

To learn whether and in what ways the columns of an inherently updatable view can be modified, query the USER_UPDATABLE_COLUMNS data dictionary view. The information displayed by this view is meaningful only for inherently updatable views. For a view to be inherently updatable, the following conditions must be met:

Each column in the view must map to a column of a single table. For example, if a view column maps to the output of a TABLE clause (an unnested collection), then the view is not inherently updatable.

The view must not contain any of the following constructs:
1) A set operator
2) a DISTINCT operator
3) An aggregate or analytic function
5) A collection expression in a SELECT list
6) A sub query in a SELECT list
7) A sub query designated WITH READ ONLY
8) Joins, with some exceptions, as documented in Oracle Database Administrator's Guide

In addition, if an inherently updatable view contains pseudo columns or expressions, then you cannot update base table rows with an UPDATE statement that refers to any of these pseudo columns or expressions.

answer Feb 9, 2015 by Amit Kumar Pandey