ORA-54012 oddity and a workaround

sokrates@11.2 > create package p is
2 function func return int deterministic;
3 end p;
4 /

Package created.

sokrates@11.2 > create table t(
2 i int,
3 func as ( p.func() )
4 );
func as ( p.func() )
*
ERROR at line 3:
ORA-54012: virtual column is referenced in a column expression

oops ? what’s wrong ?

http://docs.oracle.com/cd/E11882_01/server.112/e17766/e53000.htm#sthref13803
says

ORA-54012: virtual column is referenced in a column expression
Cause: This virtual column was referenced in an expression of another virtual column

which is not true: there is no “another virtual column”, there is only one virtual column.
It seems, I cannot name a virtual column the same as its generating function – at least in a “create table”, we have to split this “create table” in a “create table” and an “alter table”.

So

sokrates@11.2 > create table t(
2 i int,
3 fun as ( p.func() )
4 );

Table created.

sokrates@11.2 > alter table t rename column fun to func;

Table altered.

So we end up in a table for which the SQL generated by dbms_metadata cannot be replayed, see:

sokrates@11.2 > select dbms_metadata.get_ddl('TABLE', 'T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "SOKRATES"."T"
   (    "I" NUMBER(*,0),
        "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

sokrates@11.2 > drop table t purge;

Table dropped.

sokrates@11.2 > REM copy & and paste the above DBMS_METADATA - output
sokrates@11.2 > CREATE TABLE "SOKRATES"."T"
  2   (    "I" NUMBER(*,0),
  3        "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
  4   ) SEGMENT CREATION DEFERRED
  5  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  6  TABLESPACE "USERS";
      "FUNC" NUMBER GENERATED ALWAYS AS ("SOKRATES"."P"."FUNC"()) VIRTUAL VISIBLE
      *
ERROR at line 3:
ORA-54012: virtual column is referenced in a column expression