Facing an error : table or view does not exist

Question

I am using insert statement and trying to insert data into the database table. I am using stored procedures.
But I am getting this error while doing so.

Message: ORA-00942: table or view does not exist ORA-06512

I checked if the tables/stored procedures are present or not and everything is in place. Also there is no typo in table names or in sp. If I run the part of SP from query editor it works fine but when I execute the entire SP it throws an error.

I tried the steps provided by Stephen but since I have logged in with the same user/owner when I run Grant command it gives me an error saying 'Cannot Grant/revoke on own'.
One more addition to this. I have a stored procedure SP1 in which I am using a select statement as

Select a from table_name where condition;

When I execute this seperately, it returns me some results. But when I execute sp it gives an error at the same line where it is written.

Can anyone help me out to resolve this issue. I am using SQL +.
Thanks in advance Vijay

Answer 1

Justin's answer is correct but let me expand a bit.

Everyone who said that the table doesn't exist didn't read your whole post. Since you are able to:

If I run the part of SP from query editor it works fine

Obviously the table is there. Obviously you have some access to it. Otherwise this wouldn't work when it clearly does.

but when I execute the entire SP it throws an error.

This is because Oracle distinguishes between permissions granted directly and those granted via a role.

Say I do this:

Create Table TABLE_A
Create Role READ_ONLY
Grant Select on TABLE_A to READ_ONLY
Grant READ_ONLY to VIJAY

In a SQL Window/prompt you could query that table without issue. So now you need to create a view

Create VIJAY.VIEW_A as SELECT * FROM TABLE_A

You'll get the error that TABLE_A does exist. Because a view is compiled, like a procedure it runs without any roles. Since it runs without the READ_ONLY role, it's blind to the fact that TABLE_A exists. Now what I need to do is

Grant Select on TABLE_A to VIJAY.

Now that you have a direct permission, you can compile a view or procedure/package that uses that table.