Unable To Create A Materialized View While @ Symbol (Doc ID 1323435.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

While creating a Materialized View through master table with @ symbol, then the following error occurs:

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view.

For example:

SQL> select count(decode ( instr ( EMAIL , '@' ) , 0 , '@oracle.com' , null ,
null , substr ( EMAIL , instr ( EMAIL , '@' ) ) )) EMAIL from
hr.EMPLOYEES;

EMAIL

----------

107


SQL> CREATE MATERIALIZED VIEW LOG ON hr.EMPLOYEES WITH PRIMARY KEY
INCLUDING NEW VALUES;

Materialized view log created.

SQL> create materialized view hr.EMPLOYEES_01
2 REFRESH fast ON COMMIT AS
3 SELECT EMPLOYEE_ID,
4 FIRST_NAME,
5 LAST_NAME,
6 EMAIL,
7 PHONE_NUMBER,
8 HIRE_DATE,
9 JOB_ID,
10 SALARY,
11 COMMISSION_PCT,
12 MANAGER_ID,
13 DEPARTMENT_ID
14 from hr.EMPLOYEES
15 /

Materialized view created.

SQL> create materialized view hr.EMPLOYEES_02
2 REFRESH fast ON COMMIT AS
3 SELECT EMPLOYEE_ID,
4 FIRST_NAME,
5 LAST_NAME,
6 decode ( instr ( EMAIL , '@' ) , 0 , '@oracle.com' , null , null ,
substr ( EMAIL , instr (EMAIL , '@' ) ) ) EMAIL ,
7 PHONE_NUMBER,
8 HIRE_DATE,
9 JOB_ID,
10 SALARY,
11 COMMISSION_PCT,
12 MANAGER_ID,
13 DEPARTMENT_ID
14 from hr.EMPLOYEES
15 /
from hr.EMPLOYEES
*
ERROR at line 14:
ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view.


SQL> create materialized view hr.EMPLOYEES_03
2 REFRESH fast ON COMMIT AS
3 SELECT EMPLOYEE_ID,
4 FIRST_NAME,
5 LAST_NAME,
6 decode ( instr ( EMAIL , '/' ) , 0 , '/oracle.com' , null , null ,
substr ( EMAIL , instr (EMAIL , '/' ) ) ) EMAIL ,
7 PHONE_NUMBER,
8 HIRE_DATE,
9 JOB_ID,
10 SALARY,
11 COMMISSION_PCT,
12 MANAGER_ID,
13 DEPARTMENT_ID
14 from hr.EMPLOYEES;

Materialized view created.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms