Skip to Main Content
  • Questions
  • Can I change an existing column to a computed column with an Alter Table statement

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Aubreyh.

Asked: March 04, 2021 - 4:34 pm UTC

Last updated: March 05, 2021 - 5:03 am UTC

Version: 12c

Viewed 100+ times

You Asked

Test case can be found at: https://livesql.oracle.com/apex/livesql/s/lgh3hduetd3lqd74jvuolc78k

Given:
Drop table t1;
Create table t1
    (c1 Int Default 0 Not Null,
     c2 Int Default 0 Not Null);


Is it then possible to:
Alter table t1
    Modify c2 as (c1 + 1);


When I do I get the following result, so I am beginning to believe this cannot be done:
Table dropped

Table created

Alter table t1
Modify c2 as (c1 + 1)

ORA-54026: Real column cannot have an expression

Appreciate your help as I have not been able to find any documentation that would confirm this behaviour.

Thanks,
Aubrey

with LiveSQL Test Case:

and we said...

Yes, but in a couple of steps

SQL> Create table t1
  2      (c1 Int Default 0 Not Null,
  3       c2 Int Default 0 Not Null);

Table created.

SQL>
SQL> Alter table t1 set unused column c2;

Table altered.

SQL> alter table t1 add c2 int generated always as ( c1+1);

Table altered.


Here's a video about using set unused and not using drop column



More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.