CODEWITHSUNDEEP
sqloracle-database
Mark T.
Mark T.

Reputation: 567

Is it possible to make a virtual column no longer virtual in Oracle?

We have an Oracle table defined with a (nullable) virtual column. For a number of reasons, we've decided that this column should actually be generated in advance and populated as normal. Is it possible to modify the column so that it is no longer virtual without dropping and adding it back?

The following does not work:

alter table mytable modify virtualcolumn varchar2(255)

It returns successfully, but the generation function remains in place. I don't see anything about removing the "virtual" property in the Oracle 11g syntax diagrams, which leads me to believe that this is not currently possible.

Upvotes: 4

Views: 3469

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

No, a virtual column per se cannot be "materialized". If there is a need to make a virtual column permanent, add a new column to the table, update it with values from the virtual column and then drop that virtual column.

The alter table DML statement, that you've provided in your question, will not transform virtual column into a permanent one. It returns successfully simply because new and old data types of the virtual column are the same(you can't change data type of a virtual column without changing data type of underlying expression). Their sizes, though, may differ.

Upvotes: 5

Related Questions