oracle “ORA-00942: 表或视图不存在”仅在存储过程中运行时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30850479/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
'ORA-00942: table or view does not exist' only when running within a Stored procedure
提问by Chad
This should be easy pickin's for a PL-SQL person. Before you mark this question a duplicate, please ensure that while the error message may be common that the underlying problem is the same as a previous question. If so, please provide a link to the exact logical duplicate question that has been resolved. I
对于 PL-SQL 人员来说，这应该很容易选择。在将此问题标记为重复之前，请确保虽然错误消息可能很常见，但潜在问题与上一个问题相同。如果是这样，请提供指向已解决的确切逻辑重复问题的链接。一世
When I log onto my schema, I execute the following PL-SQL code:
当我登录到我的模式时，我执行以下 PL-SQL 代码：
DECLARE
v_rpt_per_key NUMBER := 0;
BEGIN
SELECT MAX(rpt_per_key)
INTO v_rpt_per_key
FROM rxfinods_sta.hd_invc_ln_item_dtl_stat;
dbms_output.PUT_LINE('v_RPT_PER_KEY=' || v_rpt_per_key);
END;
/
The query executes successfully and the max value of RPT_PER_KEYis written to the Output Window in Toad.
However, when I execute essentially the same code in a procedure.
查询成功执行，最大值RPT_PER_KEY写入 Toad 的输出窗口。
但是，当我在一个过程中执行基本相同的代码时。
CREATE OR REPLACE PROCEDURE hd_purge_test
IS
v_rpt_per_key NUMBER := 0;
BEGIN
SELECT MAX(stat.rpt_per_key)
INTO v_rpt_per_key
FROM rxfinods_sta.hd_invc_ln_item_dtl_stat stat;
--HD_INVC_LN_ITEM_DTL_STAT
dbms_output.PUT_LINE('v_RPT_PER_KEY=' || v_rpt_per_key);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END hd_purge_test;
I get an error that the table does not exist.
我收到表不存在的错误。
[Warning] ORA-24344: success with compilation error
14/21 PL/SQL: ORA-00942: table or view does not exist
9/4 PL/SQL: SQL Statement ignored
(1: 0): Warning: compiled but with compilation errors
Since I was able to query the table when using the same credentials, this proves that my ID has access to select from the table. Shouldn't I have rights to also query the table from a stored procedure that I created underr the same logged on schema? Do some additional grants need to be executed?
由于我能够在使用相同凭据时查询表，这证明我的 ID 有权从表中进行选择。我不应该也有权从我在相同登录模式下创建的存储过程中查询表吗？是否需要执行一些额外的赠款？
Note: The procedure compiles successfully if I select from any table in the logged on schema.
注意：如果我从登录模式中的任何表中进行选择，则该过程编译成功。
回答by Boneist
Sounds like an issue with select privileges granted via a role, rather than directly to the schema. See ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function).
听起来像是通过角色授予的选择权限而不是直接授予架构的问题。请参阅ORA-00942：表或视图不存在（在单独的 sql 时有效，但在 oracle 函数内无效）。
回答by kevinsky
Check that your user has a direct grant to access the table rather than a 'grant select to xxx_role'
检查您的用户是否具有访问表的直接授权，而不是“授予选择 xxx_role”
回答by mahi_0707
Can you execute the below and then compile your procedure again:
您能否执行以下命令，然后再次编译您的程序：
--GRANT ALL ON <YOUR TABLE NAME> TO <YOUR USER NAME>;
GRANT ALL ON RXFINODS_STA.HD_INVC_LN_ITEM_DTL_STAT TO chadD;
This will give full permission on the object.
这将授予对该对象的完全权限。
回答by Riche Risus
or you can do this:
或者你可以这样做：
CREATE OR REPLACE PROCEDURE hd_purge_test
AUTHID CURRENT_USER
AUTHID CURRENT_USER
IS
v_rpt_per_key NUMBER := 0;
BEGIN
SELECT MAX(stat.rpt_per_key)
INTO v_rpt_per_key
FROM rxfinods_sta.hd_invc_ln_item_dtl_stat stat;
--HD_INVC_LN_ITEM_DTL_STAT
dbms_output.PUT_LINE('v_RPT_PER_KEY=' || v_rpt_per_key);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END hd_purge_test;
