×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Modify columns - parts of virtual column
2

Modify columns - parts of virtual column

Modify columns - parts of virtual column

(OP)
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 10,2 to NUMBER 11,2

  STATION        VARCHAR2(20 BYTE),
  LAND_VALUE     NUMBER(10,2)                   DEFAULT 0                     NOT NULL,
  IMPROVE_VALUE  NUMBER(10,2)                   DEFAULT 0                     NOT NULL,
  PURCHASE_AMT   NUMBER GENERATED ALWAYS AS ("LAND_VALUE"+"IMPROVE_VALUE"),
  AREA           NUMBER(12,2)                   DEFAULT 0                     NOT NULL, 

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.

RE: Modify columns - parts of virtual column

(OP)
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. sad
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.

RE: Modify columns - parts of virtual column

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

RE: Modify columns - parts of virtual column

(OP)
I did try that, but I could not find the correct syntax sad


---- Andy

There is a great need for a sarcasm font.

RE: Modify columns - parts of virtual column

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

CODE

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!

RE: Modify columns - parts of virtual column

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

CODE

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.

RE: Modify columns - parts of virtual column

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

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


RE: Modify columns - parts of virtual column

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


---- Andy

There is a great need for a sarcasm font.

RE: Modify columns - parts of virtual column

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!!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close