virtual column


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 11.2.0.3, corrected on 11.2.0.4.

Example:

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

SQL> connect u1/u1
Connected.

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

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
Connected.

SQL> drop user u1 cascade;
User dropped.