Skip to Main Content
  • Questions
  • PL/SQL update set row command and virtual columns

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, lh.

Asked: May 31, 2017 - 2:22 pm UTC

Last updated: November 28, 2019 - 3:52 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi

I have found PL/SQL 'update tablex set row' command very useful in some cases. (And special thanks to asktom.com to teaching me it !).

However I now would like to add virtual columns to table to be updated.
My trial caused error of 'trying to update virtual column'.

Is there a way to get around this ?


lh

and Chris said...

As you're on 12c, yes there is a workaround.

From 12.1 on, you can mark columns as invisible. This means they no longer appear for generic column access, such as select *. And %rowtype.

So you can make your virtual column invisible and use set row to update the other columns:

create table t (
  x int , 
  y int as ( x + 1 )
);

insert into t values (1, default);

declare
  rw t%rowtype;
begin
  rw.x := 2;
  
  update t
  set    row = rw;
end;
/

ORA-54017: UPDATE operation disallowed on virtual columns

alter table t modify y invisible;
select * from t;

X  
1  

declare
  rw t%rowtype;
begin
  rw.x := 2;
  
  update t
  set    row = rw;
end;
/
select * from t;

X  
2 

select x, y from t;

X  Y  
2  3 


The column is still there. But you need to select it explicitly to see it.

Whether or not this workaround is a good idea I'll leave for the reader to ponder ;)

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Never would have thought this.

A reader, May 31, 2017 - 6:38 pm UTC

This might be feasible in this case...

This method may cause some problems.

lh, November 28, 2019 - 12:02 pm UTC

Hi

This mechanism You described, which I would never have thought, has worked sofar very well.
However just run into one caveat

ORA-7445 [qcsjRmCol] When Creating View From Base Table With Invisible Virtual Columns (Doc ID 2337257.1)

lh
Chris Saxon
November 28, 2019 - 3:52 pm UTC

Thanks for sharing