Impossible to change virtual column

On my series of using datapump with remap_schema and virtual columns, I end up yesterday with a new case:

– impossible to change a virtual column after the referenced function does not exist anymore.

This bug is present on, corrected on


SQL> grant connect,resource to u1 identified by u1;
Grant succeeded.

SQL> connect u1/u1

SQL> create or replace function f1 (v1 number)
  return number DETERMINISTIC as
  return v1;

Function created.

-- Create table with virtual column referencing the function

SQL> create table t1 (c1 number, c2 number as (f1(c1)));
Table created.

SQL> drop function f1;
Function dropped.

-- Try to change the virtual column. Impossible!
SQL> alter table t1 drop column c2;

alter table t1 drop column c2
ERROR at line 1:
ORA-00904: "U1"."F1": invalid identifier

-- Workaround is to recreate the function...

-- clean up
SQL> connect / as sysdba

SQL> drop user u1 cascade;
User dropped.

Leave a comment

Your email address will not be published. Required fields are marked *