Came across this odd behaviour today (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production):
SQL> CREATE TABLE test_it
(col1 NUMBER,
col2 NUMBER,
colv VARCHAR2(1) GENERATED ALWAYS AS (CASE WHEN col1 > col2 THEN 'Y' ELSE 'N' END) VIRTUAL)
/
Table created.
SQL> ALTER TABLE test_it MODIFY
(colv GENERATED ALWAYS AS (CASE WHEN col1 < col2 THEN 'Y' ELSE 'N' END) VIRTUAL)
/
Error at line 2 ORA-54017: UPDATE operation disallowed on virtual columns
It will let me make other changes to the column:
SQL> ALTER TABLE test_it MODIFY ( colv GENERATED ALWAYS AS (TO_CHAR(col1)) VIRTUAL)
/
Table altered
But it doesn't like that CASE statement in an ALTER TABLE.
Does anybody else get this problem? How do I work around it? I guess I could drop the column and recreate it with the new definition, but I'm open to other suggestions.