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.

It appears that the _current user_ does not have a synonym for `MY_TABLE`. — Abra, Feb 19 '22 at 07:56
A package cannot know that you want to select from `prp` schema unless you explicitly specify it — astentx, Feb 19 '22 at 08:19
Granting the privilege on the table has nothing to do with your error. It's a matter of name resolution. I may have a key to enter your house (privilege) but it does me no good if I don't know which house is yours. When you reference a table without specifying the owner (schema) that it belongs to, oracle will assume it is in the schema of the current user. It's not going to go looking through all the other schemas to find a table of that name. And even if it did, how would it deal with multiple schemas having the same table name? — EdStevens, Feb 19 '22 at 14:25

score 0 · Answer 1 · answered Feb 19 '22 at 11:39

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.

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

1 Answers1