ORA-00942: table or view does not exist in Package based Procedure

Question

I have gone through similar threads but unable to understand the root cause of the error.

I have an Oracle Schema as "PRP".

PRP has one table Named : "MY_TABLE".

PRP has one package Named : "My_PKG" with authid current_user

Package contains following procedure

PROCEDURE CUSTOMER_ORDER_QUERY (
        P_REPORT             OUT SYS_REFCURSOR
    ) AS
    BEGIN
            OPEN P_REPORT FOR SELECT
                              * from MY_TABLE

END;

When I execute the procedure from the package it gives the error Table or view does not exists.

But when I prefix the schema name to the table in the procedures the cursor executes perfectly.

I have explicitly given privileges on that table to same schema.

from sys users : grant all on prp.MY_TABLE to PRP;

But none helps.

The Package and the table is in same schema.

Please help.

Answer 1

I did what you described; no problems.

Create user prp and grant required privileges:

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> create user prp identified by prp
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

SQL> grant create session, create table, create procedure to prp;

Grant succeeded.

Connect as prp, create table and package:

SQL> connect prp/prp
Connected.
SQL> create table my_table as
  2  select 1 id, 'Little' name from dual union all
  3  select 2   , 'Foot'        from dual;

Table created.

SQL> create or replace package my_pkg as
  2    procedure customer_order_query (p_report out sys_refcursor);
  3  end;
  4  /

Package created.

SQL> create or replace package body my_pkg as
  2    procedure customer_order_query (p_report out sys_refcursor)
  3    as
  4    begin
  5      open p_report for select * from my_table;
  6    end;
  7  end;
  8  /

Package body created.

Testing:

SQL> var l_rc refcursor
SQL> exec my_pkg.customer_order_query (:l_rc);

PL/SQL procedure successfully completed.

SQL> print l_rc;

        ID NAME
---------- ------
         1 Little
         2 Foot

SQL>

So, yes - it works. If both table and package belong to the same user (reside in the same schema), you don't need any additional privileges as you own both of them.