表都存在,查询的时候都是可以的。但是加上create or replace view usr_portal.v_milestone_user_info as 就提示ora-00942 表或视图不存在 ,这是什么问题?单独执行下面的几个select都是可以的create or replace view usr_portal.v_milestone_user_info as
Select ZGH As USERID,XM As Name,a.DWDM As deptcode,b.DM As deptname,'' email,'' workplace,'' workphone,1 As metier From USR_DATAI.T_JZG a Left Join USR_ZXBZ.T_ZXBZ_DW b On a.DWDM=b.LBM
UNION
Select XH As USERID,XM As Name,a.YXDM As deptcode,b.DM As deptname,'' email,'' workplace,'' workphone,3 As metier From USR_DATAI.T_BZKS a Left Join USR_ZXBZ.T_ZXBZ_DW b On a.YXDM=b.LBM
Union
Select XH As USERID,XM As Name,a.YXDM As deptcode,b.DM As deptname,'' email,'' workplace,'' workphone,2 As metier From USR_DATAI.T_YJS a Left Join USR_ZXBZ.T_ZXBZ_DW b On a.YXDM=b.LBM
Select ZGH As USERID,XM As Name,a.DWDM As deptcode,b.DM As deptname,'' email,'' workplace,'' workphone,1 As metier From USR_DATAI.T_JZG a Left Join USR_ZXBZ.T_ZXBZ_DW b On a.DWDM=b.LBM
UNION
Select XH As USERID,XM As Name,a.YXDM As deptcode,b.DM As deptname,'' email,'' workplace,'' workphone,3 As metier From USR_DATAI.T_BZKS a Left Join USR_ZXBZ.T_ZXBZ_DW b On a.YXDM=b.LBM
Union
Select XH As USERID,XM As Name,a.YXDM As deptcode,b.DM As deptname,'' email,'' workplace,'' workphone,2 As metier From USR_DATAI.T_YJS a Left Join USR_ZXBZ.T_ZXBZ_DW b On a.YXDM=b.LBM
是否有权限访问该用户?
把usr_portal.v_milestone_user_info改成 v_milestone_user_info试试
如果是的话,那么在存储过程中,role授权是被失效的,因此会报这样的错误。
解决办法是表对象的直接授权。