SQL 为什么 Oracle 不告诉您哪个表或视图不存在？

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

提问by erickson

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?

如果您使用过 Oracle，您可能会收到有用的消息“ORA-00942：表或视图不存在”。消息不包含丢失对象的名称是否存在合法的技术原因？

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.

关于这是出于安全原因的争论听起来像是由 TSA 精心设计的。如果我是一名攻击者，我会知道我刚刚试图利用哪个表，并且能够轻松解释这个无用的消息。如果我是一名通过多层应用程序代码处理复杂连接的开发人员，通常很难分辨。

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?

是否有一种对开发人员友好（而不是招聘 DBA）的方法来确定丢失表的名称？

---

---

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.

尽管我已经接受了与该主题相关的答案，但它并没有真正回答我的问题：为什么错误消息中不是名称的一部分？如果有人能想出真正的答案，我很乐意改变我的投票。

采纳答案by Ethan Post

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.

您可以在参数文件（纯文本或 spfile）中设置一个 EVENT 以强制 Oracle 在 user_dump_dest 中转储详细的跟踪文件，对象名称可能在那里，如果不是 SQL 应该在那里。

EVENT="942 trace name errorstack level 12"

EVENT="942 跟踪名称错误堆栈级别 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.

如果您使用纯文本文件，则需要将所有 EVENT 设置保留在连续行上。不确定这如何应用于 spfile。

回答by Nick Pierpoint

SQL*Plus does tell you the table that doesn't exist. For example:

SQL*Plus 确实会告诉您不存在的表。例如：

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.

这里显示了缺失表的名称和出错的SQL语句中的行号。

Similarly, in a one-line SQL statement you can see the asterisk highlighting the name of the unknown table:

同样，在单行 SQL 语句中，您可以看到突出显示未知表名称的星号：

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

就您的问题而言，我猜错误消息不包含表名的原因是错误消息本身需要是静态文本。错误行中的行号和位置清楚地传递回 SQL*Plus（以某种方式）。

回答by Nick Pierpoint

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+ 让您了解哪个表名是不可接受的。确实，它有助于直接 DML，尽管解析它非常困难。但是当涉及到动态时，我们没有任何帮助：

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

回答by Mark Nold

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.

如果您使用的是像 TOAD 或 TORA 这样的 SQL 浏览工具，它将通过突出显示或将光标移动到您出错的位置来帮助您解决 ORA 错误。

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

将您的 SQL 复制并粘贴到这些工具之一中以提供帮助。您可能还会发现可用的分析信息也很有用。

回答by Matthew Watson

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.

这并不理想，但由于没有去检查跟踪文件，我不知道该怎么做。

回答by Jon Ericson

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:

我在解释 Oracle 错误消息时从来没有遇到过问题。部分原因是我见过的用于为 Oracle 开发 SQL 的每个交互式工具都有助于指出查询出错的位置。正如其他人所指出的，这包括 SQL*Plus 和 Perl DBI 模块：

$ 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 isa 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.

嗯，这是一个有点难以阅读，因为这一切都压扁在一行。但是 GUI 工具将能够指向 Oracle 开始遇到查询问题的令牌。并在解析器上进行一些工作，您可以编写一个工具来挑选出有问题的表。

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.

为了回答根本问题，Oracle 错误似乎并没有按照您期望的方式工作。据我所知，Oracle 中的错误消息都不支持可变文本。相反，Oracle 返回两位信息：错误编号和错误发生的位置。如果您有合适的工具，就很容易用这些数据来诊断错误。可以说，Oracle 的系统比根据错误提供可变数量的诊断数据的系统更适合工具创建者。想象一下，必须为 Oracle 的所有错误消息（包括未来的错误）编写一个自定义解析器以突出显示有问题的位置。

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

1. IBM 对 System R 使用了这种错误消息样式，Larry Ellison、Bob Miner 和 Ed Oates 将其复制到构建 Oracle V2 中。（向后兼容性。）

2. 错误编号和位置是诊断信息的最小可能表示。（节俭。）

3. 正如我上面指出的，为了简化连接到 Oracle 的工具的创建。（互操作性。）

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

无论如何，我不认为您需要成为 DBA 才能弄清楚哪个表不存在。您只需要使用适当的工具。（并调整您的期望，我想。）

回答by Mark Harrison

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.

使用硬编码字符串可能被认为比在运行时冒着由于格式不正确的“%s”字符串而出现神秘故障的风险更好。

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

（并不是说我特别同意这个 POV，顺便说一句。）

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.

现在，如果您打印 PHP 等，将 Oracle 错误消息转储到浏览器，您不会特别暴露应用程序的内部工作。

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

（请参阅上面的免责声明，我也很乐意在错误中获得更多信息。）

回答by Hobo

@Matthew

@马修

Your query's a start, but it might not work when you have multiple schemas. For example, if I log into our instance as myself, I have read access to all our tables. But if I don't qualify the table name with the schema I'll get an ORA-00942 for tables without synonyms:

您的查询是一个开始，但当您有多个架构时它可能不起作用。例如，如果我以自己的身份登录我们的实例，我就拥有对所有表的读取权限。但是，如果我不使用模式限定表名，对于没有同义词的表，我将获得 ORA-00942：

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

The table still shows up in all_tables though:

该表仍然显示在 all_tables 中：

SQL> select owner, table_name from all_tables where table_name = 'TOOLS'; 

OWNER                          TABLE_NAME 
------------------------------ ------------------------------ 
APPLICATION                    TOOLS 

@erikson Sorry that doesn't help much. I'm with Mark - I used TOAD.

@erikson 抱歉，这没有多大帮助。我和马克在一起——我用过 TOAD。