"ORA-01733: virtual column not allowed here" when inserting into a view

Question

I created a view called "view_employee" like this:

CREATE VIEW view_employee AS
SELECT employee.surname || ', ' || employee.name AS comp_name, employee.sex, sections.name AS section_name, employee_age
FROM sections, employee WHERE employee.section = sections.sect_code;

And I would like to insert data into the table using the view, like this:

INSERT INTO view_employee VALUES ('Doe, John', 'm', 'Marketing', 34);

Here are the tables' columns and constraints:

create table sections(
  sect_code number(2),
  name varchar2(20),
  income number(5,2)
   constraint CK_sections_income check (income>=0),
  constraint PK_sections primary key (sect_code)
 );

 create table staff(
  ident number(5),
  document char(8),
  sex char(1)
   constraint CK_staff_sex check (sex in ('f','m')),
  surname varchar2(20),
  name varchar2(20),
  address varchar2(30),
  section number(2) not null,
  age number(2)
   constraint CK_staff_age check (age>=0),
  marital_status char(10)
   constraint CK_employee_marital_status check (marital_status in 
('married','divorced','single','widower')),
  joindate date,
   constraint PK_employee primary key (ident),
  constraint FK_employee_section
   foreign key (section)
   references sections(sect_code),
  constraint UQ_staff_document
   unique(document)
);

The error message I get when attempting to insert is the following:

Error starting at Command Line: 1 Column : 1
Error report -
SQL Error: ORA-01733: virtual column not allowed here
01733. 00000 -  "virtual column not allowed here"
*Cause:    
*Action:

How could I insert those values into the table using the view? Thanks in advance.

Translucent

Seems rather straightforward. comp_name doesn't actually exist in the table. It's a calculated column in the view. How can the engine insert the data into a column that does not exist? This cannot be done with the data you provided using the view you created. You will need to parse the first name & last name and either a) insert directly to the table or b) create a different view that contains no virtual column. – Mechanistic

Your view refers to employees, not staff; and why have you changed age to cantidadhijos (??) in a constraint? And look up instead-of triggers. But you'd have to have code in that trigger to decompose the comp_name into separate fields, which seems a bit.... odd. You're probably much better off inserting into the underlying table in this case. – Shammy

@jacob-h so the problem is the alias, right? Thank you for your help, I'll have to insert it directly to the table. – Translucent

@alex-poole typo fixed, and will take a look to the link you provided. Thanks for your time. – Translucent

Answer 1

A view must not contain any of the following constructs. So, it can be updateable.

• 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.

Copperhead

Answer 2

Try recompiling the view and any triggers on it. You may have made it invalid by adding/removing columns.

Merilynmeringue

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Zoom