果冻想
认真玩技术的地方

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日 于呼和浩特。

赞(79) 打赏
未经允许不得转载:果冻想 » Oracle存储过程ORA-00942: table or view does not exist详解
关注微信公众号
关注微信公众号和果冻一起分享你的疑惑与心得。
分享到: 更多 (0)

评论 5

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
  1. #-49

    我是小白,这个存储过程我想问一下,你看看我理解的对不?
    CREATE OR REPLACE PROCEDURE “JELLY2″.”PRINTALLSTUDENTS”–创建存储固定语法+存储name
    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’;–给变量赋值,查询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);–输出打印结果。话说不需要开启那个set serverout on这个设置也能使用打印语句吗?
    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;–结束事务代码块

    MyloveYun9个月前 (02-12)回复
    • 没有细看,整体上理解的木有问题。

      果冻想9个月前 (02-12)回复
  2. #-48

    哦豁,评论没有换行,看起来会比较麻烦,我只学过Mysql,对Oracle也不明白,最近看了一下存储过程,感觉有点像Java的函数,序列和游标也是这几天看的,只能理解到这样了

    MyloveYun9个月前 (02-12)回复
  3. #-47

    不知道可不可以换行试一试

    MyloveYun9个月前 (02-12)回复
  4. #-46

    貌似《br/》也换不了行

    MyloveYun9个月前 (02-12)回复

玩技术,我们是认真的

联系我们关于果冻

感谢支持果冻想

支付宝扫一扫打赏

微信扫一扫打赏