程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle存儲過程ORA-00942: table or view does not exist詳解

Oracle存儲過程ORA-00942: table or view does not exist詳解

編輯：Oracle數據庫基礎

今天在寫存儲過程的時候，發現一個很詭異的問題，存儲過程裡就一個最簡單的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