Why doesn't Oracle tell you WHICH table or view does not exist?

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.

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).

I would disagree with the opinion, that SQL+ lets you understand which table name is unacceptable. True, it helps in direct DML, although parsing it is very hard. But when it comes to dynamic, we get no help:

SQL> begin
  2  execute immediate 'insert into blabla values(1)';
  3  end;
  4  /
begin
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 2

If you are using a SQL browsing tool like TOAD or TORA it will help you with ORA errors by highlightling or pointing moving the cursor to where you made your error.

Copy and paste your SQL in to one of these tools to help. You may also find the analyse info available useful too.

If its not a huge statement, then the easiest way is just to check the data dictionary,

SQL> select * from xx,abc;
select * from xx,abc
                 *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select owner,table_name from all_tables where table_name in ('XX','ABC');

OWNER                          TABLE_NAME
------------------------------ ------------------------------
MWATSON                        XX

SQL>

This isn't ideal, but short of going and examining trace files, I'm not sure how else to do it.

Why doesn't Oracle tell you WHICH table or view does not exist?

Related

Recent Posts