How to permit update of a view's results in Oracle?

Question

I understand that database views are read-only, or at least read-only by default.

Is it possible to enable the change of data brought by an oracle view?

Rephrasing: If I make a view of just one table, just to hide some columns, will changes to this data be updated on the table?

Answer 1

I don't believe Oracle views are read only by default... a single table view should be updateable providing it doesn't contain multiple row operations like DISTINCT or GROUP BY. The user in question must have been granted UPDATE VIEW privs.

A simple view with columns removed should definitely be updatable proividing you have privs to update the view in question...

GRANT UPDATE ON your_view_name TO your_user;

What error(s) are you getting when you try to run the UPDATE statement?

Answer 2

Yes, Oracle views can be modified.

There are, however, some restrictions:

• views that contain set operators, aggregates, GROUP BY, DISTINCT and joins (in general) are not modifiable;
• some join views are modifiable.

Answer 3

Hi,

In oracle a view contains no base data of it's own. So if your view allows updates then the underlying table will be updated.

If you need more information it is worth looking at the "views" section in the Oracle concepts guide.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm#sthref787

Further information can also be found in the "Create View" sql command.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm#SQLRF01504

Hope this helps

Regards