Cursor in anonymous block works, but not in procedure-CodePudding

Attempting to write a query to ID (and refresh) outdated materialized views, I have written the following code - which works, when run in TOAD or SQL Plus:

CREATE OR REPLACE procedure myuser.refresh_materialized_views as
--declare 
    cursor crs_mviews is select owner, mview_name, staleness, last_refresh_date from all_mviews 
    where 
        staleness <> 'FRESH' 
    ;
    mv_row all_mviews%rowtype;
    exec_command varchar(200) default '';
    begin_time timestamp;
    end_time timestamp;
begin
    begin_time := sysdate;
    insert into myuser.MV_REFRESH_LOG values ('BEGINNING', 'SUCCESS', sysdate, sysdate,null);
    commit;
    for mv in crs_mviews
    loop
        exec_command := 'exec dbms_mview.refresh('''||mv.owner||'.'||mv.mview_name||''''||');'
            ||' -- Last refresh: '||mv.last_refresh_date||', status is '||mv.staleness;
--        dbms_output.put_line(exec_command);
--        dbms_mview.refresh(mv.owner||'.'||mv.mview_name);
        end_time := sysdate;
        insert into myuser.MV_REFRESH_LOG values (mv.mview_name, 'SUCCESS', begin_time, end_time,mv.last_refresh_date);
        commit;
    end loop;
    insert into myuser.MV_REFRESH_LOG values ('ENDING', 'SUCCESS', sysdate, sysdate,null);
    commit;
end;

It works fine when I run it in TOAD or SQL Plus (I comment out the CREATE PROCEDURE and uncomment the DECLARE). It identifies those materialized views which are stale. But when I run the procedure itself, it creates the begin / end log entries - but does not do the loop itself.

The only thing I've found online that seems to match this is this posting at Ask Tom. The description sounds spot on, but the answer doesn't seem like it addresses the issue - it just talks about permissions. I'm not getting any kind of permissions error when I create or run this procedure - it simply runs and does nothing beyond creating the BEGIN and END log entries.

I imagine it's something really obvious, but I cannot figure it out.

CodePudding user response：

You are using the all_* views in your cursor. The data you see in the all_* dictionary views is filtered to those objects you have effective permissions on. By compiling a stored procedure, you lose role grants and have only system and object grants, which can cause rows you normally see in all_* views to disappear. Most likely, you aren't looping because there's nothing in those views under your permissions without roles.

To fix, either get the DBA to grant you privs on the objects involved, or request the "select any dictionary" system priv and use the dba_* dictionary views instead of all_* views. The dba_* views are not filtered by what you have permissions on.