Creating a SQL view that will accept inserted values

I have created a SQL view that retrieves the result set I desire but when I try to add a new set of values it fails. When searching around on the net it appears that VIEW has a few limitations and some types of joins may not be allowed, I have tried to create the view a few different ways but with no success yet, would anyone happen to know a way I could rewrite this create view query that would allow me to insert new values into it?

The view:

CREATE VIEW NATIONAL_ITEMS 
AS
    SELECT item.itemno, item.itemdesc, item.itemprice
    FROM item 
    FULL OUTER JOIN invoiceitem ON item.itemno = invoiceitem.itemno
    WHERE item.itemdesc LIKE '%National%' 
      AND invoiceitem.invoiceno IS NULL
    WITH CHECK OPTION;

My insert statement that does not work:

INSERT INTO NATIONAL_ITEMS 
VALUES ('123-456', 'National TV', 100);

I get this error:

Error starting at line : 1 in command –
INSERT INTO NATIONAL_ITEMS VALUES (‘123-456’, ‘National TV’, 100)
Error at Command Line : 1 Column : 1
Error report –
SQL Error: ORA-01733: virtual column not allowed here
01733. 00000 – “virtual column not allowed here”

Any help would be greatly appreciated, thank you.

Answer

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;