Home » Server Options » Replication » why i can't call dbms_mview.refresh in my proc?
why i can't call dbms_mview.refresh in my proc? [message #48603] Fri, 03 December 2004 03:19 Go to next message
Martin Tian
Messages: 5
Registered: October 2004
Junior Member
i can run mv refresh like this:

SQL> exec DBMS_MVIEW.REFRESH('EDEN_SUPL_MV',null,null,true,false,1,0,0,true);

PL/SQL procedure successfully completed

SQL>

-------------------------------------------------------------------

but can't do like this:

SQL>create or replace procedure myproc is
begin
  DBMS_MVIEW.REFRESH('EDEN_SUPL_MV',null,null,true,false,1,0,0,true);
end myproc;

SQL> exec myproc;

begin myproc; end;

ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at "EDENFR.MYPROC", line 3
ORA-06512: at line 1

SQL>

------------------------------------------------------

WHY?PLEASE HELP!

Report message to a moderator

Re: why i can't call dbms_mview.refresh in my proc? [message #48616 is a reply to message #48603] Fri, 03 December 2004 12:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Frequently when something works from SQL*Plus but not from a stored procedure, it is because the necessary privileges are contained within a role. Procedures do not use roles, only privileges that are granted directly. If this is the problem, you will need to grant the necessary privileges directly. To test whether this is the problem, you can issue SET ROLE NONE from SQL*Plus, then try your execution from SQL*Plus that has been working. If it was dependent upon a role, then it won't work from SQL*Plus with SET ROLE NONE.

Report message to a moderator

Re: why i can't call dbms_mview.refresh in my proc? [message #48641 is a reply to message #48616] Mon, 06 December 2004 18:31 Go to previous message
Martin Tian
Messages: 5
Registered: October 2004
Junior Member
Oh,yes,thank you,Barbara,I've grant "select any dictionary" privillage and it works!

Report message to a moderator

Previous Topic: Materialized Views
Next Topic: ORA-12054 while creating materialized view
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Feb 06 01:21:14 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.