Facing error: table or view does not exist

Question

I am using the insert statement and trying to insert data into a database table. I use stored procedures.
But at the same time I get this error.

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

I checked if tables / stored procedures are present or not, and everything is in place. There are also no typos in the table names or in sp. If I run the SP part from the 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 logged in with the same user / owner when I run the Grant command, it gives me the error message "Can not Grant / revoke on own".
Another addition to this. I have a stored procedure SP1 in which I use the select statement as

Select a from table_name where condition; 

When I do this separately, it returns me some results. But when I execute sp, it throws an error in the same line where it is written.

Can anyone help me solve this problem. 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 does not exist did not read your entire post. Since you can:

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

Obviously, there is a table. Obviously you have access to it. Otherwise, it will not work if it is clear.

but when I execute the whole SP, it throws an error.

This is because Oracle distinguishes between permissions granted directly and granted through the role.

Let's 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 the SQL Window / prompt, you can query this table without problems. So now you need to create a view

 Create VIJAY.VIEW_A as SELECT * FROM TABLE_A 

You will receive an error message that TABLE_A exists. Since the view is compiled, like a procedure, it runs without any role. Since it works without the READ_ONLY role, it does not ensure that TABLE_A exists. Now i need to do

 Grant Select on TABLE_A to VIJAY. 

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

Answer 2

Is there a table in the schema where the procedure is stored? If not, the simplest explanation is that the owner of your procedure was granted access to the table through the role, not through a direct grant. The stored procedure of the rights of a qualifier must have direct access to the objects to which it refers. A quick way to check this is to disable the roles for the session, i.e.

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

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

Answer 3

In Oracle, you can choose whether the stored procedure will be executed with invoker or qualifier rights: http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/subprograms.htm#i18574

Verify that the AUTHID property of the stored procedure is correct and if the recipient has the appropriate permissions.

Answer 4

Well, very simple, the table you are trying to insert data into does not exist in the database you are connected to. You need to check both of these things (that is, what you are connected to, and there is a table there and available for the user context that you are using).

Answer 5

As Joe Stefanelli said. There are many possibilities for the error shown here.

Check:

• You are connecting to the correct instance of Oracle.
• You have permission to query or execute processing on the table that you reference in your query.
• There is a difference between regular statements and select procedures. Procedures in oracle do not take into account the roles assigned to the user; rather, permission should be explicitly granted to the user. For more information read the following link ORA-00942