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?