Updating table through view in oracle

Insert, Update and Delete SQL Views You can use the "OR Replace" option.If the view exists it will be replaced with the new definition or a new view will be created.We can use Create or Replace option to create views instead of dropping the view and recreating it, as with this option the privileges granted on the view are preserved, but the dependent stored programs and view become invalid.

updating table through view in oracle-32updating table through view in oracle-29updating table through view in oracle-10

When a view is created, Oracle Database expands any wildcard (*) in a top-level view query into a column list.

To be more specific, a view is not updatable if it contains any of the following: are illegal and are rejected.

(Note that even if a view is updatable, it might not be possible to insert into it, as described elsewhere in this section.) The updatability of views may be affected by the value of the system variable. Earlier discussion in this section pointed out that a view is not insertable if not all columns are simple column references (for example, if it contains columns that are expressions or composite expressions).

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.

Leave a Reply