PL/SQL (MOSC)

MOSC Banner

Refresh any snapshot by any user in a procedure

edited Feb 23, 2010 6:07PM in PL/SQL (MOSC) 25 commentsAnswered
I am trying to create a procedure that will allow any user to refresh any materialized view.  I am trying to get the basic construct to work.  Security issue will be addressed later.

1  I created this procedure SYS:

 CREATE OR REPLACE PROCEDURE SYS.SLU_RFR_MAT_VIW_PRC AS
BEGIN
  EXECUTE IMMEDIATE 'GRANT ALTER ANY MATERIALIZED VIEW TO TST';
  DBMS_SNAPSHOT.REFRESH ('PROFILE.PRO_EVENT_ITEMS_COURSE_V_SS', 'C');;
  EXECUTE IMMEDIATE 'REVOKE ALTER ANY MATERIALIZED VIEW FROM TST';
END;
/
CREATE OR REPLACE PUBLIC SYNONYM SLU_RFR_MAT_VIW_PRC FOR SYS.SLU_RFR_MAT_VIW_PRC;
GRANT ALL ON SLU_RFR_MAT_VIW_PRC TO PUBLIC;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

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

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center