I am trying to insert a row in a table using VIEW
as
INSERT INTO FIELDI18N(LANGUAGE_ID) VALUES (1);
but it gives me following error:
Error starting at line 5 in command:
INSERT INTO FIELDI18N(LANGUAGE_ID) VALUES (1)
Error at Command Line:5 Column:22
Error report:
SQL Error: ORA-01733: virtual column not allowed here
01733. 00000 - "virtual column not allowed here"
*Cause:
*Action:
Any Clue ?
Added the View Definition:
CREATE OR REPLACE VIEW FIELDI18N("FIELDID", "NAME", "TYPE", "DESCRIPTION", "LANGUAGE_ID")
AS
(SELECT field.fieldid,
field.type,
NVL(i18n.name, field.name) name,
NVL(i18n.description, field.description) description,
i18n.language_id
FROM fields field
JOIN i18n_fields i18n
ON (field.fieldid = i18n.fieldid)
);
A virtual column is a table column whose values are calculated automatically using other column values, or another deterministic expression. Here is the syntax of a virtual column: column_name [data_type] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
3 Updatable and Insertable Views. Some views are updatable and references to them can be used to specify tables to be updated in data change statements. That is, you can use them in statements such as UPDATE , DELETE , or INSERT to update the contents of the underlying table.
Answer: 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. So, yes, you can update the data in an Oracle VIEW providing you have the proper privileges to the underlying Oracle tables.
LANGUAGE_ID is probably a calculated field, or in any case the database cannot infer what change is to be made to the tables underlying the view based on the change you are requiring. Have to see the view definition code to know.
I believe that in order to do an insert or update using a view that all of the tables in the view must be joined via a primary key. This is to prevent duplicates caused by the view which cannot be updated.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With