4

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?

11

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.

3

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;
/
1

Although this is an old question, I'll post this answer for the future:

If you're using 12c, you can add a column as INVISIBLE, which will avoid this problem. Using this, you can continue to use %rowtype, but the column will also not appear in select * statements.

This can be convenient for columns added just for performance.

Oracle article on invisible columns

  • How does an invisible column help performance? – Vérace Dec 14 '17 at 17:59
  • I have no idea what INVISIBLE columns are. J.Born, perhaps you can expand on that or provide a link. – ypercubeᵀᴹ Dec 14 '17 at 18:38
  • 1
    I looked these up - and I fail to see much use for these INVISIBLE columns. You should never be issuing SELECT * FROM my_tab anyway - it should always be SELECT col1,,, coln FROM my_tab. I'm genuinely puzzled as to what use they could be put to. – Vérace Dec 14 '17 at 20:48
  • Sorry Verace, I meant that if you add a generated column (for performance, but you don't want it "visible" all the time) it helps to get that out of the way for other cases. I've personally used them to be able to add generated columns, and still be able to use %rowtype in procedures – J. Born Dec 20 '17 at 20:48
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.