5

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.

  • 1
    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. – Jacob H Nov 22 '17 at 18:43
  • Your view refers to `employees`, not `staff`; and why have you changed `age` to `cantidadhijos` (??) in a constraint? And look up [instead-of triggers](https://docs.oracle.com/database/121/LNPLS/triggers.htm#GUID-9F06D45C-7C60-434E-A597-114A0C445671). 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. – Alex Poole Nov 22 '17 at 19:02
  • @jacob-h so the problem is the alias, right? Thank you for your help, I'll have to insert it directly to the table. –  Nov 22 '17 at 19:04
  • @alex-poole typo fixed, and will take a look to the link you provided. Thanks for your time. –  Nov 22 '17 at 19:07

2 Answers2

6

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.
Mohammed Ait
  • 139
  • 2
  • 8
0

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

Sal
  • 1
  • 1
    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](/help/how-to-answer). – Community Jul 12 '23 at 14:50