ORA-54002: only pure functions can be specified in a virtual column expression

The ORA-54002: only pure functions can be specified in a virtual column expression error is thrown when trying to specify a virtual column that uses non-deterministic functions.

create table tq84_ora_54002 ( col_1 number, col_2 varchar2(3), now as (sysdate) );

REGEXP_REPLACE

Apparently, regexp_replace is not a deterministic function, the following create table statement throws a ORA-54002 error:

create table tq84_ora_54002 (
   mm_yy     varchar2(5),
   yyyy_mm   as (
      regexp_replace(mm_yy, '^(\d\d)\.(\d\d)$', '20\2 \1')
   )
);

It is possible, however, to create a deterministic function that calls regexp_replace:

create or replace function tq84_regexp_replace(
   val   varchar2,
   regex varchar2,
   subst varchar2
) return varchar2
  DETERMINISTIC
  authid definer
as
begin
   return regexp_replace(val, regex, subst);
end tq84_regexp_replace;
/

With this function, it is possible to create the table:

create table tq84_ora_54002 (
   mm_yy    varchar2(5),
   yyyy_mm   as (
      tq84_regexp_replace(mm_yy, '^(\d\d)\.(\d\d)$', '20\2 \1')
   )
);

Test data:

begin
 insert into tq84_ora_54002(mm_yy) values ('01.13');
 insert into tq84_ora_54002(mm_yy) values ('04.18');
 insert into tq84_ora_54002(mm_yy) values ('09.17');
 commit;
end;
/

select * from tq84_ora_54002;

Cleaning up

drop table tq84_ora_54002;

ORA-54002: only pure functions can be specified in a virtual column expression

REGEXP_REPLACE

See also