果冻想
认真玩技术的地方

Oracle存储过程ORA-00942: table or view does not exist详解

怎么就不对了

今天在写存储过程的时候,发现一个很诡异的问题,存储过程里就一个最简单的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日 于呼和浩特。

未经允许不得转载:果冻想 » Oracle存储过程ORA-00942: table or view does not exist详解
网站维护离不开您的支持,您可以赞助本站,谢谢支持
×

感谢您的支持,我们会一直保持!

扫码支持
请土豪扫码随意打赏

打开支付宝扫一扫,即可进行扫码打赏哦

分享从这里开始,精彩与您同在

赞助本站
关注微信公众号
关注微信公众号和果冻一起分享你的疑惑与心得。
分享到: 更多 (0)

玩技术,我们是认真的

联系我们关于果冻