Can NOT create Materialized View

M. Mehta

It seems that you can not create a materialized view if you are using
outer joins...can someone please verify this?

Thanks
M. Mehta

Please follow my example below:

created 2 tables:

select * from emp;

EMP_ID DEPT_ID SAL
---------- ---------- ----------
1 1 20000
2 1 50000

select * from dept;

DEPT_ID DEPT_NAME
---------- ----------
1 HR
2 IT
3 CC

create materialized view (with out outer join):
SQL> create materialized view mv_test build immediate refresh complete
on commit as
2 select count(*), b.dept_id, b.dept_name, sum(sal) salary,
count(sal)
3 from emp a, dept b
4 where a.dept_id = b.dept_id
5 group by b.dept_id, b.dept_name
6 /

Materialized view created.

SQL> drop materialized view mv_test;

create materialized view (WITH outer join):
SQL> create materialized view mv_test build immediate refresh complete
on commit as
2 select count(*), b.dept_id, b.dept_name, sum(sal) salary,
count(sal)
3 from emp a, dept b
4 where a.dept_id = b.dept_id (+)
5 group by b.dept_id, b.dept_name
6 /
from emp a, dept b
*
ERROR at line 3:
ORA-12054: cannot set the ON COMMIT refresh attribute for the
materialized view

Jul 19 '05 #1

Frank van Bortel

M. Mehta wrote:

It seems that you can not create a materialized view if you are using
outer joins...can someone please verify this?

Thanks
M. Mehta

Please follow my example below:

created 2 tables:

select * from emp;

EMP_ID DEPT_ID SAL
---------- ---------- ----------
1 1 20000
2 1 50000

select * from dept;

DEPT_ID DEPT_NAME
---------- ----------
1 HR
2 IT
3 CC

create materialized view (with out outer join):
SQL> create materialized view mv_test build immediate refresh complete
on commit as
2 select count(*), b.dept_id, b.dept_name, sum(sal) salary,
count(sal)
3 from emp a, dept b
4 where a.dept_id = b.dept_id
5 group by b.dept_id, b.dept_name
6 /

Materialized view created.

SQL> drop materialized view mv_test;

create materialized view (WITH outer join):
SQL> create materialized view mv_test build immediate refresh complete
on commit as
2 select count(*), b.dept_id, b.dept_name, sum(sal) salary,
count(sal)
3 from emp a, dept b
4 where a.dept_id = b.dept_id (+)
5 group by b.dept_id, b.dept_name
6 /
from emp a, dept b
*
ERROR at line 3:
ORA-12054: cannot set the ON COMMIT refresh attribute for the
materialized view

Versions may help... this is 9.2.0.4:
SQL> connect system/manager
Connected.
SQL> grant create materialized view to hr;

Grant succeeded.

SQL> connect hr/hr
Connected.
SQL> @t

Materialized view created.

SQL> l
1 create materialized view mv_test build immediate refresh complete
2 on commit as
3 select count(*), b.department_id, b.department_name, sum(salary)
salary,cou
nt(salary)
4 from employees a, departments b
5 where a.department_id = b.department_id (+)
6* group by b.department_id, b.department_name
--

Regards,
Frank van Bortel

Jul 19 '05 #2

M. Mehta

I gave the following permission: grant create materialized view to hr;
and I still get the following error:ORA-12054: cannot set the ON
COMMIT refresh attribute for the materialized view.

Here is what I am doing: ( Release 9.2.0.4.0 )

CREATE MATERIALIZED VIEW mv_test BUILD IMMEDIATE REFRESH COMPLETE
ON COMMIT AS
SELECT COUNT(*), b.dept_id, b.dept_name, SUM(sal) salary,COUNT(sal)
FROM EMP a, DEPT b
WHERE a.dept_id = b.dept_id (+)
GROUP BY b.dept_id, b.dept_name

Frank van Bortel <fv********@netscape.net> wrote in message news:<c8**********@news3.tilbu1.nb.home.nl>...

M. Mehta wrote:

It seems that you can not create a materialized view if you are using
outer joins...can someone please verify this?

Thanks
M. Mehta

Please follow my example below:

created 2 tables:

select * from emp;

EMP_ID DEPT_ID SAL
---------- ---------- ----------
1 1 20000
2 1 50000

select * from dept;

DEPT_ID DEPT_NAME
---------- ----------
1 HR
2 IT
3 CC

create materialized view (with out outer join):
SQL> create materialized view mv_test build immediate refresh complete
on commit as
2 select count(*), b.dept_id, b.dept_name, sum(sal) salary,
count(sal)
3 from emp a, dept b
4 where a.dept_id = b.dept_id
5 group by b.dept_id, b.dept_name
6 /

Materialized view created.

SQL> drop materialized view mv_test;

create materialized view (WITH outer join):
SQL> create materialized view mv_test build immediate refresh complete
on commit as
2 select count(*), b.dept_id, b.dept_name, sum(sal) salary,
count(sal)
3 from emp a, dept b
4 where a.dept_id = b.dept_id (+)
5 group by b.dept_id, b.dept_name
6 /
from emp a, dept b
*
ERROR at line 3:
ORA-12054: cannot set the ON COMMIT refresh attribute for the
materialized view

Versions may help... this is 9.2.0.4:
SQL> connect system/manager
Connected.
SQL> grant create materialized view to hr;

Grant succeeded.

SQL> connect hr/hr
Connected.
SQL> @t

Materialized view created.

SQL> l
1 create materialized view mv_test build immediate refresh complete
2 on commit as
3 select count(*), b.department_id, b.department_name, sum(salary)
salary,cou
nt(salary)
4 from employees a, departments b
5 where a.department_id = b.department_id (+)
6* group by b.department_id, b.department_name

Jul 19 '05 #3

M.Kovacic

Maybe will help, if you create on base tables "MATERIALIZED VIEW LOG"

regards, Matej

"M. Mehta" <ml****@yahoo.com> wrote in message
news:a7**************************@posting.google.c om...

I gave the following permission: grant create materialized view to hr;
and I still get the following error:ORA-12054: cannot set the ON
COMMIT refresh attribute for the materialized view.

Here is what I am doing: ( Release 9.2.0.4.0 )

CREATE MATERIALIZED VIEW mv_test BUILD IMMEDIATE REFRESH COMPLETE
ON COMMIT AS
SELECT COUNT(*), b.dept_id, b.dept_name, SUM(sal) salary,COUNT(sal)
FROM EMP a, DEPT b
WHERE a.dept_id = b.dept_id (+)
GROUP BY b.dept_id, b.dept_name

Frank van Bortel <fv********@netscape.net> wrote in message

news:<c8**********@news3.tilbu1.nb.home.nl>...

M. Mehta wrote:

It seems that you can not create a materialized view if you are using
outer joins...can someone please verify this?

Thanks
M. Mehta

Please follow my example below:

created 2 tables:

select * from emp;

EMP_ID DEPT_ID SAL
---------- ---------- ----------
1 1 20000
2 1 50000

select * from dept;

DEPT_ID DEPT_NAME
---------- ----------
1 HR
2 IT
3 CC

create materialized view (with out outer join):
SQL> create materialized view mv_test build immediate refresh complete
on commit as
2 select count(*), b.dept_id, b.dept_name, sum(sal) salary,
count(sal)
3 from emp a, dept b
4 where a.dept_id = b.dept_id
5 group by b.dept_id, b.dept_name
6 /

Materialized view created.

SQL> drop materialized view mv_test;

create materialized view (WITH outer join):
SQL> create materialized view mv_test build immediate refresh complete
on commit as
2 select count(*), b.dept_id, b.dept_name, sum(sal) salary,
count(sal)
3 from emp a, dept b
4 where a.dept_id = b.dept_id (+)
5 group by b.dept_id, b.dept_name
6 /
from emp a, dept b
*
ERROR at line 3:
ORA-12054: cannot set the ON COMMIT refresh attribute for the
materialized view

Versions may help... this is 9.2.0.4:
SQL> connect system/manager
Connected.
SQL> grant create materialized view to hr;

Grant succeeded.

SQL> connect hr/hr
Connected.
SQL> @t

Materialized view created.

SQL> l
1 create materialized view mv_test build immediate refresh complete
2 on commit as
3 select count(*), b.department_id, b.department_name, sum(salary)
salary,cou
nt(salary)
4 from employees a, departments b
5 where a.department_id = b.department_id (+)
6* group by b.department_id, b.department_name

Jul 19 '05 #4