ORA-00942: table or view does not exist - Error even when Table exists and Select query runs fine

1
votes

I am getting this error when I am trying to use the table in a Stored Procedure. When I do a select * from table, i get results. But when I put it in the Stored Procedure (join with other table) and compile, I get the error, Error(269,17): PL/SQL: ORA-00942: table or view does not exist

Any idea on why this is happening?

2
oracleoracle11g
only select from that table works in stored procedure ? – Frank
Perhaps you only have access to the table via a grant to a role. You need a direct grant to your schema to use the table in a procedure. – Tony Andrews

2 Answers

3
votes

To use a table from one schema in a stored procedure owned by another schema, the procedure's schema needs a direct grant on the table:

grant select on ALPS.CUST_LOOKUP_DTL to myschema;

A grant to a role that the schema has is not enough:

grant select on ALPS.CUST_LOOKUP_DTL to some_role;
grant some_role to myschema;

In this case myschema can select from the table, but its procedures cannot.

1
votes

What happens when you execute what you have in your stored procedure? You mention a join table - that not the same as select * from table. You may have to fully qualify the table name by schema name.

select * from schema.table;