Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Virtual column not allowed here

Tags:

sql

oracle

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)
  );
like image 618
Ram Dutt Shukla Avatar asked Apr 22 '13 07:04

Ram Dutt Shukla


People also ask

What is virtual column in Oracle?

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]

Can we insert data into view in Oracle?

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.

Can we update the view in Oracle?

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.


2 Answers

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.

like image 92
David Aldridge Avatar answered Sep 22 '22 05:09

David Aldridge


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.

like image 21
Hugh Seagraves Avatar answered Sep 19 '22 05:09

Hugh Seagraves