ORA-54002 when trying to create Virtual Column using REGEXP_REPLACE on Oracle 12cR2

I encountered an issue today trying to create a table in an Oracle 12cR2 database, the DDL for which, I extracted from an Oracle 11gR2 database. The error returned when trying to create the table was:

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

The definition of the table included a Virtual Column which used a REGEXP_REPLACE call to derive a value from another column on the table.

Here is a simplified test case illustrating the scenario (Thanks Tim for the REGEXP_REPLACE example code):

select * from v$version
/
create table test_ora54002_12c(
 col1 VARCHAR2(20 CHAR) NOT NULL
 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
)
/
drop table test_ora54002_12c purge
/

Running this on 11gR2 gives:

SQL> select * from v$version
 2 /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

5 rows selected.

Elapsed: 00:00:00.40
SQL> create table test_ora54002_12c(
 2 col1 VARCHAR2(20 CHAR) NOT NULL
 3 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
 4 )
 5 /

Table created.

Elapsed: 00:00:00.24
SQL> drop table test_ora54002_12c purge
 2 /

Table dropped.

Running this on 12cR2 gives:

SQL> select * from v$version
/
 2
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0

SQL> create table test_ora54002_12c(
 col1 VARCHAR2(20 CHAR) NOT NULL
 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
)
/
 2 3 4 5 ,virtual_column1 VARCHAR2(4000 CHAR) GENERATED ALWAYS AS(REGEXP_REPLACE(col1, '([A-Z])', ' \1', 2)) VIRTUAL VISIBLE
 *
ERROR at line 3:
ORA-54002: only pure functions can be specified in a virtual column expression


SQL> drop table test_ora54002_12c purge
/
 2 drop table test_ora54002_12c purge
 *
ERROR at line 1:
ORA-00942: table or view does not exist

As you can see, 12cR2 gives the ORA-54002 error.

Looking on MOS, highlights this article, which suggests that you shouldn’t have been able to do this in 11gR2, i.e. it was a bug and that 12cR2 has fixed this bug and thus you can no longer create such a virtual column (the article refers to functional index and check constraint use cases as well).

In my case, I was able to rewrite the virtual column to use simple string functions such as SUBSTR, TRANSLATE and INSTR to achieve what I wanted and the virtual column was allowed to be created with these – problem solved – a shame really as the REGEXP_REPLACE approach was far neater.

Feel free to comment...