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.
41 Answer
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.
