CODEWITHSUNDEEP
oracle-databaseplsqlnumbersgenerated
Raj More
Raj More

Reputation: 48034

Change from Number to Generated in PLSQL

I am trying to alter a table where the original datatype of this column is Number to a generated column, but I get an error of "ORA-00905 Missing Keyword"

Alter Table MyTable
Modify Column FlagColumn NUMERIC (38,0) GENERATED ALWAYS AS (CASE  WHEN ValueColumn IS NULL THEN 1 ELSE 0 END) VIRTUAL;

Is my syntax correct?

Do I have any other options besides dropping and recreating the table?

Upvotes: 2

Views: 41

Answers (1)

Allan
Allan

Reputation: 17429

The Oracle documentation pretty clearly doesn't support the syntax you're attempting. The obvious solution is to drop the column, then replace it:

ALTER TABLE mytable
   DROP COLUMN flagcolumn;

ALTER TABLE mytable
   ADD numeric GENERATED ALWAYS AS (CASE WHEN valuecolumn IS NULL THEN 1 ELSE 0 END) VIRTUAL;

There's really no reason not to do this, since you're getting rid of the column's original data in any case.

Upvotes: 2

Related Questions