怎么就不对了

今天在写存储过程的时候，发现一个很诡异的问题，存储过程里就一个最简单的SELECT动态SQL语句。编写完成以后，执行这个存储过程，总是提示ORA-00942: table or view does not exist（ERROR -942 ORA-00942: 表或视图不存在）这个错误，但是我将这个SQL语句复制出来，单独在命令行运行，而又没有任何错误。很纠结，很纳闷，很无解，好好的一个SELECT语句，在命令行里就可以执行，放到存储过程就会出错了，怎么就不对了？

先剧透

后来经过Google，分析和总结，最终找到了答案。为了满足有的读者就是为了找到解决问题的答案，而并不需要接下来长篇大论的分析，我这里就先给出答案。

角色在函数、存储过程、触发器中都是失效的，也就是说，用户从角色继承过来的权限，不能在函数、存储过程、触发器中使用。在函数、存储过程、触发器中，如果要访问其他用户的对象，需要显式地给用户授予访问的权限。

上面就是解决ORA-00942: table or view does not exist（ERROR -942 ORA-00942: 表或视图不存在）这个错误的答案。如果你的存储过程中访问了其他用户的对象，而你当前登陆用户的权限是以角色的形式赋予的，并非显式赋予的，这个时候这个错误。明白人看到这里应该知道怎么解决了，如果你还是一头雾水，请允许我继续唠叨，看看下面详细的分析。

再详解

我现在在我的测试库上模拟一个出现ORA-00942: table or view does not exist（ERROR -942 ORA-00942: 表或视图不存在）这个错误的场景。

-- SYS用户创建两个用户，并赋予DBA角色权限
create user jelly identified by 123456;
create user jelly2 identified by 123456;

grant dba to jelly; -- 角色赋权
grant dba to jelly2; -- 角色赋权

-- jelly用户创建表tb_student，并插入测试数据
create table tb_student(id varchar2(10), name varchar2(20), age number(3), sex varchar2(2));
insert into tb_student values('68003001', '果冻', 23, 'M');
insert into tb_student values('68003002', '史大为', 24, 'M');
insert into tb_student values('68003003', '李艳', 23, 'F');
insert into tb_student values('68003004', '郝丽', 25, 'F');
insert into tb_student values('68003005', '佟东', 24, 'M');

select * from tb_student;

现在使用jelly2用户登录Oracle，运行以下语句：

-- jelly2用户查询表tb_student
select * from jelly.tb_student;

发现SQL语句可以正确的运行，这并没有问题。接下来，我们在jelly2用户下创建一个存储过程，访问jelly用户下的jelly.tb_student表，源码如下：

CREATE OR REPLACE PROCEDURE "JELLY2"."PRINTALLSTUDENTS" 
as
    type t_cur is ref cursor;
    cursor_stu t_cur;
    id varchar2(10);
    name varchar2(20);
    age number(3);
    sex varchar2(2);
    strSql varchar2(400);
begin
    strSql := 'select * from jelly.tb_student';
    open cursor_stu for strSql;
    loop
        fetch cursor_stu into id, name, age, sex;
        dbms_output.put_line('ID:' || id || ', NAME:' || name || ', AGE:' || age || ', SEX:' || sex);
        exit when cursor_stu%notfound or cursor_stu%notfound is null;
    end loop;
    close cursor_stu;
exception
    when others then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
        close cursor_stu;
end;

对上面代码不是很熟悉的伙计，请参见这篇《Oracle学习笔记——批处理利器游标》。在命令行运行这个存储过程：

SQL> set serveroutput on;
SQL> execute jelly2.PRINTALLSTUDENTS
ERROR -942 ORA-00942: 表或视图不存在
PL/SQL procedure successfully completed

好了，现在就模拟出现了这个错误。

具体解决办法

上面也说了，由于对用户jelly2赋予的是DBA角色，而角色权限在存储过程中是失效的，所以，为了防止访问权限失效，我们需要显示的对jelly2用户赋予访问jelly.tb_student表的权限。

grant select any table to jelly2;

经过显示的赋权，我们再次执行存储过程，就发现不会有问题了。

SQL> set serveroutput on;
SQL> execute jelly2.PRINTALLSTUDENTS
ID:68003001, NAME:果冻, AGE:23, SEX:M
ID:68003002, NAME:史大为, AGE:24, SEX:M
ID:68003003, NAME:李艳, AGE:23, SEX:F
ID:68003004, NAME:郝丽, AGE:25, SEX:F
ID:68003005, NAME:佟东, AGE:24, SEX:M
ID:68003005, NAME:佟东, AGE:24, SEX:M
PL/SQL procedure successfully completed

总结

好了，问题基本到此就总结问题了。学习，重要的是心态；学习，重要的是心态；学习，重要的是心态。重要的内容说三遍。这么一个小的知识点，如果你不会，可能真的可能坑死你，让你很无奈，边学习，边成长，边总结。也希望你能把你工作中遇到的问题，总结出来，分享出来，很期待和你一起分享学习的乐趣。

2015年9月14日 于呼和浩特。