This content has been marked as final. Show 5 replies
That might have been a good improvement to the error message,
but for whatever reason they didn't do it that way. There might
even be cases where the table is not explicitly referred to by the
JDBC client, such as in calling a stored procedure, and/or the table
should not be exposed to the JDBC client. Just a thought, but
mostly the first sentence is the basic.
I am wondering why the missing table name isn't shown, when I get this exception:
Because it may not be a 'table' that is missing and Oracle may not be able to determine what it is that should be being referenced.
The full text is
ORA-00942: table or view does not exist
Cause: The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required to access the table. If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it.
Action: Check each of the following:
the spelling of the table or view name.
that a view is not specified where a table is required.
that an existing table or view name exists.
Contact the database administrator if the table needs to be created or if user or application privileges are required to access the table.
Also, if attempting to access a table or view in another schema, make certain the correct schema is referenced and that access to the object is granted.
The most common reason when questions about this exception are ask in the forums is when a query works when run by the user but throws this exception when executed in a stored procedure or other PL/SQL code.
For that use case the usual problem is that the required prviliges have not been granted.
Roles are DISABLED during PL/SQL processing. A simple 'SELECT * FROM SCOTT.EMP' will throw that exception if the query is run in a PL/SQL procedure and the user has not been granted SELECT privileges directly. Granting the privilege to a role and then granting that role to the user will NOT work.
Tranks for your insights.
I think an exception should always give you a clue what exactly went wrong and not that there is some problem you have to debug.
Today I got this exception again than I moved a table to a different schema and it did not find it. The real reason was that I used Hibernate with auto generating id, so a new sequence + permission was required.
It would be so much easier for troubleshooting if the exception message would contain more information.