Menu
If you've used Oracle, you've probably gotten the helpful message "ORA-00942: Table or view does not exist". Is there a legitimate technical reason the message doesn't include the name of the missing object?
Arguments about this being due to security sound like they were crafted by the TSA. If I'm an attacker, I'd know what table I just attempted to exploit, and be able to interpret this unhelpful message easily. If I'm a developer working with a complex join through several layers of application code, it's often very difficult to tell.
My guess is that when this error was originally implemented, someone neglected to add the object name, and now, people are afraid it will break compatibility to fix it. (Code doing silly things like parsing the error message will be confused if it changes.)
Is there a developer-friendly (as opposed to recruiting your DBA) way to determine the name of the missing table?
Although I've accepted an answer which is relevant to the topic, it doesn't really answer my question: Why isn't the name part of the error message? If anyone can come up with the real answer, I'll be happy to change my vote.
230
This error will occur when the table name prefix is not specified for the table. The Table name prefix is a requirement for loading in bulk mode for Oracle 9i. 1) For Solution, enter CR with a Workaround if a direct Solution is not available.
In SQL Server you can use the OBJECTPROPERTY() function to check an object's type. More specifically, you can check whether it is or isn't a specific type. For example, the IsTable property tells you whether or not it's a table, the IsView property tells you whether or not it's a view, etc.
If your table does not show, then it does not exist, and you'll need to look into why it doesn't exist. Or, if you're using SQL Developer, you can check the table exists by expanding the Tables section on the left side of the screen. If you see the table there, it means it exists and you're the owner.
You can also check the data dictionary to see if a table exists: SQL> select table_name from user_tables where table_name='MYTABLE'; Another way to test if a table exists is to try to drop the table and catch the exception if it does not exist. and include the URL for the page.
You can set an EVENT in your parameter file (plain text or spfile) to force Oracle to dump a detailed trace file in the user_dump_dest, the object name might be in there, if not the SQL should be.
EVENT="942 trace name errorstack level 12"
If you are using a plain text file you need to keep all your EVENT settings on consecutive lines. Not sure how that applied to spfile.
169
SQL*Plus does tell you the table that doesn't exist. For example:
SQL> select 2 * 3 from 4 user_tables a, 5 non_existent_table b 6 where 7 a.table_name = b.table_name; non_existent_table b * ERROR at line 5: ORA-00942: table or view does not exist Here it shows that the name of the missing table and the line number in the SQL statement where the error occurs.
Similarly, in a one-line SQL statement you can see the asterisk highlighting the name of the unknown table:
SQL> select * from user_tables a, non_existent_table b where a.table_name = b.table_name; select * from user_tables a, non_existent_table b where a.table_name = b.table_name * ERROR at line 1: ORA-00942: table or view does not exist In terms of your question, I guess the reason the error message doesn't include the name of the table is that the error message itself needs to be static text. The line number and location in the line of the error is clearly passed back to SQL*Plus (somehow).
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
