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 Taod. 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.