Re: Can NOT create Materialized View

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 <fvanbortel_at_netscape.net> wrote in message news:<c8tg9u$d6n$1_at_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> _at_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
Received on Tue May 25 2004 - 22:03:57 CEST