If the where clause includes references to multiple tables, update on a view throws an “ORA-1733: Virtual column not allowed here” when the with check option clause is present.
I am attempting to implement something like an assertion without using the Materialized View + CHECK constraint trick. (I'm really just play with the with check option clause)
I suspect this is a “feature” (ie not allowed or not implemented) instead of a bug.
- Why does a
where clause that only places limits on a single table work but one that crosses multiple tables doesn't?
- Am I correct in assuming this is a “feature”?
- or am I doing something wrong?
DB Version: 19c Always Free ATP on OCI & 23.4 FREE on VirtualBox
example code
note - assume there are multiple vehicles; each with their on child table & updateable view
-- 19c version
create table vehicles (
id int generated always as identity primary key,
VIN varchar2(100) not null,
is_active number(1) default on null 1 -- 23ai: boolean default on null true
);
create table vehicle_cars (
id int references vehicles(id) on delete cascade primary key,
state char(2),
plate# varchar2(30)
);
/* 23ai - goal use DOMAIN for assertion durring DML on VIEW
create domain legal_car_dom as (
is_active as boolean,
state as char(2),
plate# as varchar2(30)
)
check (is_active is false or (is_active is true and ( state is not null and plate# is not null ) ) );
*/
create or replace
view cars ( id, vin, is_active, state, plate#)
as
select v.id, v.vin, v.is_active, c.state, c.plate#
from vehicles v
join vehicle_cars c on v.id=c.id
--where domain_check( legal_car_dom, v.is_active, c.state, c.plate# ) is true -- GOAL: 23ai attempt
where v.is_active = 0 or c.state is not null -- ora-1733 on UPDATE
--where c.state > 'A' or c.state is null -- this works
with check option;
;
insert into vehicles ( is_active, VIN ) values (1, '12344566890' );
insert into vehicles ( is_active, VIN ) values (0, '5432112345' );
insert into vehicle_cars values ( 1, 'VA', 'ABC-1234' );
insert into vehicle_cars values ( 2, null, null );
commit;
-- validate you can see both
select * from cars;
update cars set is_active = 0;
update cars set is_active = 1;