00942 ora 表存在_“ ORA-00942：表或视图不存在”仅在存储过程中运行时

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

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;

/

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

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.

解决方案