CODEWITHSUNDEEP
oracle-databaseoracle11g
Minu
Minu

Reputation: 242

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

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?

Upvotes: 1

Views: 11239

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132680

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.

Upvotes: 4

farmer
farmer

Reputation: 47

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;

Upvotes: 1

Related Questions