匿名块中游标正常执行，存储过程中游标循环未运行

我写了一个用来识别并刷新过期物化视图的PL/SQL存储过程，代码如下。在TOAD或SQL Plus中以匿名块形式运行时（注释掉CREATE OR REPLACE PROCEDURE，打开DECLARE注释），能正常识别状态非FRESH的物化视图；但创建成存储过程运行时，仅生成BEGINNING和ENDING日志条目，游标循环完全没执行，且无权限报错。

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;

排查思路 #

• 权限差异问题：匿名块用当前登录用户权限执行，存储过程默认用定义者（DEFINER）权限运行。检查myuser是否有SELECT ALL_MVIEWS的直接授权（而非通过角色），因为存储过程运行时不会激活角色权限。可执行授权命令：GRANT SELECT ON SYS.ALL_MVIEWS TO myuser;
• 视图可见性限制：ALL_MVIEWS仅显示当前用户有权访问的物化视图。匿名块运行时登录用户可能能看到更多非FRESH的MV，但存储过程的定义者myuser可能无这些MV的访问权限，导致游标返回空集。可换成DBA_MVIEWS（需给myuser授权SELECT DBA_MVIEWS）验证是否是可见性问题。
• 存储过程编译状态检查：执行SELECT object_name, status, error FROM user_objects WHERE object_type='PROCEDURE' AND object_name='REFRESH_MATERIALIZED_VIEWS';，查看存储过程是否存在隐性编译错误。
• 游标空值验证：在存储过程中添加日志直接验证游标是否返回数据，比如在BEGIN块开头、游标循环前插入逻辑：

  DECLARE
      cnt NUMBER;
  BEGIN
      SELECT COUNT(*) INTO cnt FROM all_mviews WHERE staleness <> 'FRESH';
      INSERT INTO myuser.MV_REFRESH_LOG VALUES ('CURSOR_ROW_COUNT', 'INFO', sysdate, sysdate, cnt);
      COMMIT;
  

• 事务提交影响：存储过程中频繁的COMMIT可能干扰游标数据一致性，可尝试将BEGINNING的COMMIT移到游标循环之后，或去掉不必要的提交操作（除非业务强制要求）。

内容的提问来源于stack exchange，提问作者JOATMON