ORA-00942 — Why and How
If you interact with databases using any mode, like Java hibernate or Spring JDBC /data JPA, coming across SQL errors is a pretty common phenomenon. There’s a wide range of them including some which are quite straight forward while others are not.
And
ORA-00942 in my opinion is the most irritating of the lot.
WHAT in the world is ORA-00942 ?
It appears with a message saying table or view does not exist. Simply put, we are executing a SQL query which references a table or view which is not there.
Simple, Correct ?? WRONG !
This error can gruel down your precious time because its just vague. The logs won’t completely suggest why is this error appearing. Might make you trial and error through couple of possibilities.
The answer to Why — possible causes
- The most obvious one, the table or view that’s being referenced is not present.
- Attempting to access a table without proper permissions or privileges.
- Using an expression of view where a table is required or vice versa.
- Absence of schema / incorrect schema.
Steps To Resolve
First : Verify the table name. Confirm that any unauthorized synonym is not being used.
Second : Check if you have privileges. Try running a DML (Data Manipulation Language) query on your table and see whether you’re receiving the below mentioned error. If yes, raise a db ticket and request privileges for all or specific ids depending on the requirement.
Third : Be sure that the view is not being referenced where a table is required. Execute this query just to be sure.
The query should provide a result as above.
Fourth : Specify the schema. For reference check the above image. The owner is different but the table name is same which implies that the same table is present in 3 different schemas for this case. So either you’re referencing the table or view which is in multiple schemas or you’re trying to access a table or view from another schema. In both cases the schema specification is mandatory.
For my scenario I used spring data JPA and my entity signature looks something like this.
Summary
I lost a couple of hours searching for the cause of this particular error. The error seemingly harmless kept looping me around in circles because I was unsure why was it appearing (just to mention my table name was correct) and how to solve it.
Hope this helps !
