PL/SQL: ORA-00942: table or view does not exist

 

存储过程：

 

create or replace procedure updateCwshState as
f_ywlxdm_temp varchar2(2);
f_ywid_forkyxxw_temp number(10);
f_cwsh_state_temp varchar2(2);
f_id_temp number(10);
begin
        for i in(select t.id,t.f_ywlxdm,t.f_ywid_forkyxxw,t.f_cwsh_state from t_ywbltx t where (t.f_ywlxdm='04' or t.f_ywlxdm='07') and t.f_js_readstate<>'1' ) loop
          f_ywlxdm_temp:=i.f_ywlxdm;
          f_ywid_forkyxxw_temp:=i.f_ywid_forkyxxw;
          f_cwsh_state_temp:=i.f_cwsh_state;
          f_id_temp:=i.id;
          if(f_ywlxdm_temp='04') then--课题
             update oa.t_app_jfbx_cwbxforxm t set t.F_CWSHSTATE=DECODE(f_cwsh_state_temp,'0','财务正在审核','1','财务审核完毕')  where t.id=f_ywid_forkyxxw_temp;
          else--日常
             update oa.t_app_jfbx_rcfybx t set t.F_CWSHSTATE=DECODE(f_cwsh_state_temp,'0','财务正在审核','1','财务审核完毕')  where t.id=f_ywid_forkyxxw_temp;
          end if;
          if(f_cwsh_state_temp='1') then
             update t_ywbltx t set t.f_js_readstate='1' where t.id=f_id_temp;
          end if;
      end loop;
end ;

 

编译时出现如下提示：

Error: PL/SQL: ORA-00942: table or view does not exist 
.... 

Error: PL/SQL: SQL Statement ignored 
.... 

 

原因是当前用户没有oa.t_app_jfbx_cwbxforxm， oa.t_app_jfbx_rcfybx   的查询和更新的权限导致的,如果A用户在存储过程中调用B用户的对象，则必须B显示的把该对象的相应权限赋予给用户A。

 

解决办法：

使用oa用户名登录授权给需要执行存储过程的用户;

 

grant select on t_App_Jfbx_Cwbxforxm to cwjk;

grant update on t_App_Jfbx_Cwbxforxm to cwjk;
 
grant select on t_app_jfbx_rcfybx to cwjk;

grant update on t_app_jfbx_rcfybx to cwjk;

 

 

然后编译就可以正常通过了