If you recreate the table (the table that you had dropped), the Oracle VIEW will again be fine.create view view_1 as ( select key, data from some_table_or_view; ); create table table_1 ( key more_data ); create view view_2 as ( select v1.key, v1.data, t1.more_data from view_1 v1, table_1 t1 where v1= t1); create table table_2 as ( key data more_data ); create view view_3 as ( select key, data, more_data from view_2 union select key, data, more_data from table_2 ); and then doing updates using a PL/SQL function which checks the source column and updates the appropriate table, but this means I have to kept the view and the PL/SQL function in sync. CREATE OR REPLACE TRIGGER view3_trg INSTEAD OF UPDATE ON view_3 FOR EACH ROW BEGIN IF : OLD.source = 'TAB1' THEN UPDATE table_1 t1 set t1= : WHERE t1= : ; ELSIF OLD.source = 'TAB2' THEN UPDATE table_2 t2 set t2= : WHERE t2= : ; END IF; END; / CREATE OR REPLACE TRIGGER view3_trg INSTEAD OF UPDATE ON view_3 FOR EACH ROW BEGIN UPDATE table_1 t1 set t1= : WHERE t1= : OLD.key; UPDATE table_2 t2 set t2= : WHERE t2= : OLD.key; END; View_2 probably won't be updatable. SESSIONKEY = sys_context('userenv','SESSION_USER') and slb. Using the IN assures us (because there is only ONE table in the FROM clause of the view) that the rows are in fact key preserved. TIMEUnit FROM my Table A, my Slb slb, my USERPROF u1 where a.cny# = slb.cny# and nvl(a.location#,0) = slb.locationkey and slb. We only allow modifications to objects in the key preserved table. COM CREATE OR REPLACE VIEW V_my View AS 2 SELECT 3 A. The view will become invalid whenever the base table is altered.We can recompile a view using the Alter view statement, but oracle automatically recompiles the view once it is accessed.Or at least only the columns of the child table in an enforced foreign key relationship.
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.
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.
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.
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.