"ORA-00942:表或视图不存在"仅在存储过程中运行时 [英] 'ORA-00942: table or view does not exist' only when running within a Stored procedure

问题描述

对于PL-SQL人员而言，这应该很容易选择.在将此问题标记为重复问题之前，请确保尽管错误消息很常见，但潜在问题与先前的问题相同.如果是这样，请提供指向已解决的确切逻辑重复问题的链接.我

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:

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;
/

查询成功执行，并且RPT_PER_KEY的最大值写入Toad中的输出"窗口.

但是，当我在过程中执行基本相同的代码时.

The query executes successfully and the max value of RPT_PER_KEY is written to the Output Window in Toad.

However, when I execute essentially the same code in a procedure.

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

由于使用相同的凭据可以查询表，因此证明我的ID有权从表中进行选择.我是否应该有权从我在相同的登录模式下创建的存储过程中查询表?是否需要执行一些额外的补助?

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?

注意:如果我从已登录模式中的任何表中进行选择，则该过程将成功编译.

Note: The procedure compiles successfully if I select from any table in the logged on schema.

推荐答案

听起来像是通过角色而不是直接授予架构所授予的选择特权的问题.参见 ORA-00942:表或视图不存在(当使用单独的sql时有效，但在oracle函数中不起作用).

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:表或视图不存在"仅在存储过程中运行时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！