Ora 942 ошибка

asked Sep 5, 2008 at 17:45

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.

answered Sep 5, 2008 at 17:51

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

answered Sep 9, 2008 at 15:11

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.

answered Sep 5, 2008 at 17:55

Matthew WatsonMatthew Watson

14.1k9 gold badges62 silver badges82 bronze badges

I’ve never had a problem with interpreting Oracle error messages. Part of the reason is that every interactive tool I’ve seen for developing SQL for Oracle helpfully points to the location the query went wrong. That includes SQL*Plus, as others have noted, and the Perl DBI module:

$ exec_sql.pl 'select * from daul'
DBD::Oracle::db prepare failed: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 14 in 'select * from <*>daul') [for Statement "select * from daul"] at exec_sql.pl line 68.

Well, that is a bit hard to read since it’s all squished on one line. But a GUI tool would be able to point to the token where Oracle started having problems with the query. And given a bit of work on a parser, you could write a tool to pick out the offending table.

To answer the underlying question, Oracle errors don’t seem to be designed to work the way you expect. As far as I can tell, none of the the error messages in Oracle support variable text. Instead, Oracle returns two bits of information: an error number and a location where the error occurs. If you have proper tools, it’s pretty easy to diagnose an error with those pieces of data. It can be argued that Oracle’s system is nicer to tool creators than one which provides variable amounts of diagnostic data depending on the error. Imagine having to write a custom parser for all of Oracle’s error messages (including future errors) to highlight the offending location.

Sometimes including the table name would be misleading. Just knowing where things went wrong can be a huge help:

SQL> select * from where dummy = 'X';
select * from where dummy = 'X'
              *
ERROR at line 1:
ORA-00903: invalid table name

As for why Oracle chose to do thing this way, I have some speculations:

1. IBM used this style of error message for System R, which Larry Ellison, Bob Miner and Ed Oates copied to build Oracle V2. (Backward compatibility.)

2. Error number and location are the smallest possible representation of diagnostic information. (Parsimony.)

3. As I indicated above, to simplify the creation of tools that connect to Oracle. (Interoperability.)

In any case, I don’t think you need to be a DBA to figure out which table doesn’t exist. You just need to use the proper tools. (And adjust your expectations, I suppose.)

answered Apr 21, 2009 at 0:03

Reason 1: Multi-lingual interface

There is a language-specific message configuration file for your database instance. Messages are pulled out of there and translated from the pure numeric version to the numeric+text version.

It was probably considered better to have the hardcoded strings, than to run the risk at runtime of having a mysterious failure due to an improperly formatted «%s» string.

(Not that I particularly agree with this POV, btw.)

Reason 2: Security

Right now you don’t particularly expose the internal workings of your application if you print a PHP, etc, dump of an Oracle error message to the browser.

Applications would be a bit more exposed if more detail were printed by default… For example, if citibank printed a more explanatory message.

(see disclaimer above, I would be happy to get more information in the error as well.)

answered Apr 21, 2009 at 3:54

HoboHobo

7,5365 gold badges40 silver badges50 bronze badges