'ORA-00942: table or view does not exist' only when running within a Stored procedure

Question

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.

possible duplicate of [ORA-00942: table or view does not exist (works when a separate sql, but does nto work inside a oracle function)](http://stackoverflow.com/questions/6669575/ora-00942-table-or-view-does-not-exist-works-when-a-separate-sql-but-does-nto) — Boneist, Jun 15 '15 at 16:44
Possible duplicate of [oracle "table or view does not exist" from inside stored procedure](http://stackoverflow.com/questions/4198052/oracle-table-or-view-does-not-exist-from-inside-stored-procedure) — Möoz, Sep 26 '16 at 04:58

score 5 · Accepted Answer · edited May 23 '17 at 12:34

5

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

edited May 23 '17 at 12:34

Community

1
1

answered Jun 15 '15 at 16:44

Boneist

22,910
1
25
40

Thank you. For clarification. Per the referenced answer: "By default, stored procedures and SQL methods execute with the privileges of their owner, not their current user." When it says "privileges of their owner," owner of what? The stored proc? I am the owner of the proc, which is created under my schema. Also, why would it matter whether the rights were granted to a role that I belong to vs. to me directly? – Chad Jun 15 '15 at 16:55
1

because it simply matters. for pl/sql grant through role is not enough and you need direct grant https://martincarstenbach.wordpress.com/2010/05/27/the-difference-between-a-direct-grant-and-a-role-in-plsql/ – Martina Jun 15 '15 at 21:46

score 1 · Answer 2 · answered Jun 15 '15 at 16:44

1

Check that your user has a direct grant to access the table rather than a 'grant select to xxx_role'

answered Jun 15 '15 at 16:44

kevinskio

4,431
1
22
36

score 1 · Answer 3 · answered Jun 15 '15 at 20:13

1

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.

answered Jun 15 '15 at 20:13

mahi_0707

1,030
9
17

Riche Risus · Answer 4 · 2017-05-17T15:52:16.000

0

or you can do this:

CREATE OR REPLACE PROCEDURE hd_purge_test

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;

edited May 17 '17 at 15:52

answered May 17 '17 at 15:30

Riche Risus

25
6

using AUTHID CURRENT_USER helps the block to understand that it is you trying to access the same table via a procedure – Riche Risus May 17 '17 at 15:31

'ORA-00942: table or view does not exist' only when running within a Stored procedure

4 Answers4

Related