Is (and if how) it possible in Oracle to "insert into <table> values <rowtype-variable>" if <table> has virtual columns

Here's a table

create table tq84_virtual_test_without (
  col_1 number,
  col_2 number,
  col_3 number,
  col_4 number,
  col_5 number
);

with the rule that col_5's value is the sum of the other four columns.

So the table is filled accordingly:

insert into tq84_virtual_test_without values( 1, 2, 3, 4, 10);
insert into tq84_virtual_test_without values( 3, 8, 7, 5, 23);

commit;

Now, say, that there is a need to copy one row and change just one column's value. This can of course be done quite elegantly (imho, that is) with a rowtype-variable, like so

declare
  r tq84_virtual_test_without%rowtype;
begin

  select * into r from tq84_virtual_test_without where col_2 = 8;

  r.col_4 := r.col_4 - 2;
  r.col_5 := r.col_5 - 2;

  insert into tq84_virtual_test_without values r;

end;
/

This is elegant because it doesn't clutter the source code with insert into ... (col_1, col_2...) values (.., ..) statements and I'd like to keep this feature, if possible.

On the other hand, col_5 is a perfact candidate for a virtual column. So, here's almost the same thing, but with col_5 being a virtual column:

create table tq84_virtual_test_with (
  col_1 number,
  col_2 number,
  col_3 number,
  col_4 number,
  col_5 as (col_1 + col_2 + col_3 + col_4) virtual
);

insert into tq84_virtual_test_with values( 1, 2, 3, 4, default);
insert into tq84_virtual_test_with values( 3, 8, 7, 5, default);

commit;

Now, and this is unfortunate, the following construct doesn't work anymore:

declare
  r tq84_virtual_test_with%rowtype;
begin

  select * into r from tq84_virtual_test_with where col_2 = 8;

  r.col_4 := r.col_4 - 2;

--     
--  ORA-54013: INSERT operation disallowed on virtual columns
--
  insert into tq84_virtual_test_with values r;

end;
/

So, is this still somehow possible (and if so, how) to use this rowtype-variable along with virtual columns?

Use a view that excludes the virtual columns to do the manipulation. I've just tested this & it works:

create view v_tq84_virtual_test_with as ( select col_1, col_2, col_3, col_4 from tq84_virtual_test_with );

declare
  r v_tq84_virtual_test_with%rowtype;
begin

  select * into r from v_tq84_virtual_test_with where col_2 = 8;

  r.col_4 := r.col_4 - 2;

  insert into v_tq84_virtual_test_with values r;

end;
/

As far as I can tell, this is the only way to workaround your requirement to use %rowtype.

StackExchange Database Administrators Q#14631, answer score: 11