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
