Modify columns - parts of virtual column 2

[Andrzejek]

I am trying to modify 2 fields in a table, and both of them are parts of another (virtual / calculated) field.
What I want to do it change LAND_VALUE and IMPROCE_VALUE from NUMBER [highlight #FCE94F]10[/highlight],2 to NUMBER [highlight #8AE234]11[/highlight],2

[pre] STATION VARCHAR2(20 BYTE),[blue]
LAND_VALUE NUMBER([highlight #FCE94F]10[/highlight],2) DEFAULT 0 NOT NULL,
IMPROVE_VALUE NUMBER([highlight #FCE94F]10[/highlight],2) DEFAULT 0 NOT NULL,
PURCHASE_AMT NUMBER GENERATED ALWAYS AS ("LAND_VALUE"+"IMPROVE_VALUE"),[/blue]
AREA NUMBER(12,2) DEFAULT 0 NOT NULL,[/pre]

But I am getting an error:
ORA-54031: column to be dropped or modified is used in a virtual column expression

How can I change those 2 fields from 10,2 to 11,2?



---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

It looks to me that I have to drop the virtual column first, modify the two columns, and then add a new (well, old...) virtual column back. :-(
I know it is not that important, but if I want to keep the order of the columns like I have them now, I would have to rebuild the table. Such a hassle...


---- Andy

There is a great need for a sarcasm font.

 

[carp]

Could you redefine your virtual column to be non-virtual, modify your numeric column, then redefine your original virtual column back to being virtual?

 

[Andrzejek]

I did try that, but I could not find the correct syntax :-(


---- Andy

There is a great need for a sarcasm font.

 

[carp]

I've never worked with virtual columns, so I can't draw upon any real experience here. Let's assume for the moment there is no such syntax available (hard to believe, so I'll look into this later). Perhaps you could create a table that mirrors the names/positions/datatype that you want. Then you could try

INSERT INTO my_new_table (SELECT * FROM my_old_table);
DROP TABLE my_old_table;
RENAME my_new_table TO my_old_table;

I would definitely try doing this with a mockup first - that "DROP TABLE ..." stuff is always a leap of faith!

 

[carp]

OK, after playing around with virtual columns a bit, here is a way to do it:

ALTER TABLE my_table MODIFY purchase_amt GENERATED ALWAYS AS (-1); -- eliminates any column dependencies
ALTER TABLE my_table MODIFY land_value NUMBER(11,2);
ALTER TABLE my_table MODIFY improve_value NUMBER(11,2);
ALTER TABLE my_table MODIFY purchase_amt GENERATED ALWAYS AS (land_value + improve_value); -- reinstates column dependencies

Run this as a script and the disruption time should be very small.

 

[johnherman]

have a star for the column dependencies thinking - I would've missed that

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[Andrzejek]

Nice! That's what I was after.
My DBA gave up on this - dropped virtual column, modified the two, and introduced VC again.
Thanks carp


---- Andy

There is a great need for a sarcasm font.

 

[carp]

Glad that helped, Andy. Sorry it was too late - but you might want to pass this on to your DBA to add to his/her knowledge base. And thanks for the stars!!