2

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?

Raj More
  • 47,048
  • 33
  • 131
  • 198

1 Answers1

2

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.

Allan
  • 17,141
  • 4
  • 52
  • 69