Updating data in a view oracle

Rated 3.90/5 based on 873 customer reviews

So, yes, you can update the data in an Oracle VIEW providing you have the proper privileges to the underlying Oracle tables.

Yes, in Oracle, the VIEW continues to exist even after one of the tables (that the Oracle VIEW is based on) is dropped from the database.

So if this is not happening, you are more likely to be using a materialised view.

SQL, SQL Server, Tutorials, Oracle, PL/SQL, Interview Questions & Answers, Joins, Multiple Choice Questions, Quiz, Stored Procedures, Select, Insert, Update, Delete and other latest topics on SQL, SQL Server and Oracle.

I have updated the data from the table by the insert into the table command, but I was wondering if its possible to update the data using the view.

Thanks :) you can update data in a view, but there are very specific rules (see oracle doc) https://docs.oracle.com/cd/E17952_01/refman-5.1-en/as another person has commented, if you update the underlying data the view should reflect the change (assuming you have committed the change).

However, if you try to query the Oracle VIEW after the table has been dropped, you will receive a message indicating that the Oracle VIEW has errors.

If you recreate the table (the table that you had dropped), the Oracle VIEW will again be fine.

updating data in a view oracle-11

The view will become invalid whenever the base table is altered.

CREATE VIEW sup_orders AS SELECT suppliers.supplier_id, orders.quantity, orders.price FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id WHERE suppliers.supplier_name = 'Microsoft'; CREATE or REPLACE VIEW sup_orders AS SELECT suppliers.supplier_id, orders.quantity, orders.price FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id WHERE suppliers.supplier_name = 'Apple'; A VIEW in Oracle is created by joining one or more tables.

When you update record(s) in a VIEW, it updates the records in the underlying tables that make up the View.

dear Chris, To keep the data in the underlying table and change them (in 11.2) use: drop materialized view mv preserve table; update mv set y = 3; commit; You reinstall the materialized view by adding 'on prebuilt table' as follows: create materialized view mv on prebuilt table refresh fast with primary key as select * from t; So, the mv is again not updatable.

Hello guys I have a question on some how to update some data within a view.

Leave a Reply