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.

Answer 2

Does the table exist in the schema where the stored procedure exists? If not, the simplest explanation is that the owner of your procedure has been granted access to the table via a role not via a direct grant. A definer's rights stored procedure needs to have direct access to the objects it accesses. A quick way to test this is to disable roles for the session, i.e.

SQL> set role none;
SQL> <<execute your query>>

If that generates the error, the problem is the lack of a direct grant.

Answer 3

Well, put very simply, the table that you are trying to insert data into does not exist in the database you are connected to. You need to check both those things (i.e. what are you connected to, and is the table there and accessible for the user context you are using).

Answer 4

As Joe Stefanelli said .. there are a lot of possibilities for the error being shown here.

Check whether:

1. You are connecting to the correct Oracle Instance.
2. You have permissions to query or perform processing on table that you are referencing in your query.
3. There is a difference between ordinary select statements and procedures. Procedures in oracle do not respect the roles assigned to a user; rather the permission needs to be explicitly granted to the user. For more information read the following linkORA-00942

Answer 5

In Oracle you can choose if the stored procedure is executed with the rights of the invoker or the definer: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/subprograms.htm#i18574

Check if the AUTHID property of the stored procedure is correct and if the resulting user has appropriate permissions.