In Oracle, How to Insert or Update a Record Through a View

In Oracle, is it possible to INSERT or UPDATE a record through a view?

Views in Oracle may be updateable under specific conditions. It can be tricky, and usually is not advisable.

From the Oracle 10g SQL Reference:

Notes on Updatable Views

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:
  
  
  • A set operator
  • a DISTINCT operator
  • An aggregate or analytic function
  • A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
  • A collection expression in a SELECT list
  • A subquery in a SELECT list
  • A subquery designated WITH READ ONLY
  • Joins, with some exceptions, as documented in Oracle Database Administrator's Guide

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

If you want a join view to be updatable, then all of the following conditions must be true:

• The DML statement must affect only one table underlying the join.
• For an INSERT statement, the view must not be created WITH CHECK OPTION, and all columns into which values are inserted must come from a key-preserved table. A key-preserved table is one for which every primary key or unique key value in the base table is also unique in the join view.
• For an UPDATE statement, all columns updated must be extracted from a key-preserved table. If the view was created WITH CHECK OPTION, then join columns and columns taken from tables that are referenced more than once in the view must be shielded from UPDATE.
• For a DELETE statement, if the join results in more than one key-preserved table, then Oracle Database deletes from the first table named in the FROM clause, whether or not the view was created WITH CHECK OPTION.

How to update a view in oracle

You can create an instead of trigger on the view, as described in the documentation:

A view presents the output of a query as a table. If you want to change a view as you would change a table, you must create INSTEAD OF triggers. Instead of changing the view, they change the underlying tables.

Once you have that trigger in place, you can update the view using the same syntax as if it was a table.

You haven't shown your view or table definitions so there isn't enough information to provide a useful example; fortunately the documentation has one you can use as a starting point.

How to update or create a new record in a table when a view is used to display existing records in Oracle Apex?

You can use views as a proxy for table updates, but they need to remain key-preserved, as the error suggests.

This means you should at least be selecting the primary key in the view, and don't join anywhere that loses that PK uniqueness.

Sometimes it make complain because ambiguity exists, such as a missing foreign key constraint. Further examples explored here

You can still include these vital columns in APEX so that you can define them in the meta-data accordingly as the primary key, you can just set them to 'hidden'.

But you've still got to set the value for new records somwhere, such as a computation, trigger, or use an identity column.

Creating a SQL view that will accept inserted values

You have two options for the view in order to insert in the view.

First, Your View must contain all the keys present in the table on which you are creating the view i.e there must be no DISTINCT OR GROUP BY clause.

If you performing JOIN on the tables same is applicable for all join tables, all keys from the table must be present in the view and there must be no DISTINCT OR GROUP BY clause.

The table which has 1:1 row relationship with the rows in the view called Key preserving table

Second, you can create instead of trigger on the view. The trigger will be fired instead of INSERT, UPDATE, OR DELETE and in the trigger, you can handle the DML statement.

Since your table is not key preserving then you can make use of INSTEAD OF TRIGGER.

CREATE OR REPLACE TRIGGER NATIONAL_ITEMS_TRG
INSTEAD OF INSERT
ON NATIONAL_ITEMS
FOR EACH ROW
BEGIN
    INSERT INTO ITEM(itemno, itemdesc, itemprice) VALUES (:NEW.itemno, :NEW.itemdesc, :NEW.itemprice);
END;

INSERT and UPDATE a record using cursors in oracle

This is a highly inefficient way of doing it. You can use the merge statement and then there's no need for cursors, looping or (if you can do without) PL/SQL.

MERGE INTO studLoad l
USING ( SELECT studId, studName FROM student ) s
ON (l.studId = s.studId)
WHEN MATCHED THEN
  UPDATE SET l.studName = s.studName
   WHERE l.studName != s.studName
WHEN NOT MATCHED THEN 
INSERT (l.studID, l.studName)
VALUES (s.studId, s.studName)

Make sure you commit, once completed, in order to be able to see this in the database.

---

To actually answer your question I would do it something like as follows. This has the benefit of doing most of the work in SQL and only updating based on the rowid, a unique address in the table.

It declares a type, which you place the data within in bulk, 10,000 rows at a time. Then processes these rows individually.

However, as I say this will not be as efficient as merge.

declare

   cursor c_data is
    select b.rowid as rid, a.studId, a.studName
      from student a
      left outer join studLoad b
        on a.studId = b.studId
       and a.studName <> b.studName
           ;

   type t__data is table of c_data%rowtype index by binary_integer;
   t_data t__data;

begin

   open c_data;
   loop
      fetch c_data bulk collect into t_data limit 10000;

      exit when t_data.count = 0;

      for idx in t_data.first .. t_data.last loop
         if t_data(idx).rid is null then
            insert into studLoad (studId, studName)
            values (t_data(idx).studId, t_data(idx).studName);
         else
            update studLoad
               set studName = t_data(idx).studName
             where rowid = t_data(idx).rid
                   ;
         end if;
      end loop;

   end loop;
   close c_data;

end;
/

Insert into Oracle SQL or Update if value already exist?

Merge does not work with INSERT ALL. If you want to use merge, you should make from both table a view with instead of trigger and than use merge against the view. The whole logic will be inside the trigger.

EDIT: Merge does not work with such views
ORA-38106: MERGE bei Join View oder View mit INSTEAD OF-Trigger nicht unterstützt

You can make two merge statements (one for every table) or one for insert and one for update against view:

CREATE SEQUENCE MYDICT_SEQ START WITH 1 MAXVALUE 9999999999999999999999999999 MINVALUE 0;

CREATE VIEW mydict
AS
   SELECT a.id, a.origterm, a.code, b.replaceterm, b.sysctlang
     FROM table1 a LEFT OUTER JOIN table2 b ON a.id = b.id;   

CREATE OR REPLACE TRIGGER mydict_io
   INSTEAD OF INSERT OR UPDATE OR DELETE
   ON mydict
   FOR EACH ROW
DECLARE
   cnt1   INTEGER := 0;
   cnt2   INTEGER;
   nid    NUMBER;
BEGIN
   IF INSERTING OR UPDATING THEN
      IF :new.id IS NULL AND INSERTING THEN
         nid := mydict_seq.NEXTVAL;
      ELSE
         nid := :new.id;

         IF UPDATING THEN
            nid := :old.id;
         END IF;

         SELECT COUNT (*)
           INTO cnt1
           FROM table1
          WHERE id = nid;
      END IF;

      IF cnt1 = 0 THEN
         INSERT INTO TABLE1 (ID, ORIGTERM, CODE)
              VALUES (nID, :new.ORIGTERM, :new.CODE);
      ELSIF cnt1 > 0 THEN
         UPDATE TABLE1
            SET ORIGTERM = :NEW.ORIGTERM, CODE = :NEW.CODE
          WHERE id = nid;
      END IF;

      SELECT COUNT (*)
        INTO cnt2
        FROM table2
       WHERE id = nid AND SYSCTLANG = :new.SYSCTLANG;

      IF cnt2 = 0 THEN
         INSERT INTO TABLE2 (ID, REPLACETERM, SYSCTLANG)
              VALUES (nID, :new.REPLACETERM, :new.SYSCTLANG);
      ELSE
         UPDATE TABLE2
            SET REPLACETERM = :new.REPLACETERM
          WHERE id = nid AND SYSCTLANG = :new.SYSCTLANG;
      END IF;
   ELSIF DELETING THEN
      DELETE FROM table2
            WHERE id = :old.id AND SYSCTLANG = :old.SYSCTLANG;

      SELECT COUNT (*)
        INTO cnt2
        FROM table2
       WHERE id = nid;

      IF cnt2 = 0 THEN
         DELETE FROM table1
               WHERE id = :old.id;
      END IF;
   END IF;
END;

DECLARE
   nid   NUMBER;
BEGIN
   INSERT INTO mydict (ORIGTERM, CODE, REPLACETERM, SYSCTLANG)
        VALUES ('Plant', 'TEST', 'Pflanze', 3);
    nid := mydict_seq.currval;

   UPDATE mydict
      SET REPLACETERM = 'Fabrik'
    WHERE id = nid;

   UPDATE mydict
      SET REPLACETERM = 'Usine', SYSCTLANG = 4
    WHERE id = nid;
END;

The logic in the instead of trigger can be done better, but I leave this to you ;)

---

Related Topics