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

Question

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?

Answer 1

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.

Answer 2

I really like Phil's answer (+1). Here is an alternative that you've probably already considered. I include it here only for completeness. It satisfies your requirement for a row type, but is certainly not as elegant.

insert into ... (col_1, col_2...) VALUES (vRowType.Col_1, vRowType.Col_2...);

The flip side of this coin is that you could define a cursor with only the rows you need, but this is leads to even more code that would be nice to avoid.

DECLARE
   CURSOR c1 IS
      SELECT col_1, col_2 ... FROM t1 WHERE ...;
   r1 c2%RowType;
BEGIN
  OPEN C1;
  FETCH C1 INTO L_REC;
  INSERT INTO t1 (col_1, col_2...) VALUES r1;
  CLOSE C1;   
END;
/

Answer 3

I would recommend using a table to store table data and a view to store calculated results from that data. Mixing the two in one hybrid-object merely confuses the situation for future development efforts.

A developer coming in five years from now may assume he needs to update all the columns of the table to get accurate totals. If, instead, he was querying from a view, he would have to look at the view definition to update the data, and the proper way to update the data would become quickly apparent.

Virtual columns are cool, but such things should be used sparingly.