如何使Oracle错误消息更详细？

The message that drives me crazy is ORA-01008 - Not all variables bound.

让我疯狂的消息是ORA-01008 - 并非所有变量都受到约束。

Is there a way to know which one of the 42 possible variable names I have misspelled without staring at the monitor till my eyes pop out?

有没有办法知道我拼错了42个可能的变量名中的哪一个,而不是盯着显示器直到我的眼睛弹出来?

Update: I use ADO.NET to access the database. Perhaps it does lose some information in Oracle exceptions, as @Justin Cave has suggested. But I'm positive that the parameter name never appears even in SQL Plus.

更新:我使用ADO.NET访问数据库。正如@Justin Cave建议的那样,它可能会丢失Oracle异常中的一些信息。但我很肯定参数名称即使在SQL Plus中也不会出现。

2 个解决方案

#1

I don't know of any way to get Oracle to make the error more specific. Maybe some future version will improve this error message.

我不知道有什么办法让Oracle更具体地说明错误。也许将来某个版本会改进此错误消息。

Instead of just staring at it, though, there are other things you can try. For example, convert each variable in the SQL statement to a literal one at a time, until the error goes away. If possible, generate the list of variable names instead of typing them manually.

不过,不仅仅是盯着它,你还可以尝试其他的东西。例如,将SQL语句中的每个变量一次转换为文字变量,直到错误消失为止。如果可能,生成变量名称列表,而不是手动键入它们。

#2

In general, Oracle provides the line and column number of any errors, but it is up to the particular API you are using (unless you happen to be writing an OCI application, which is probably unlikely) as to whether and how those APIs are called. Since the answer is likely to end up being API-specific, what API are are you using and what does your code look like when the error occurs (i.e. JDBC, ODBC, OLE DB, etc)?

通常,Oracle提供任何错误的行号和列号,但是由您正在使用的特定API(除非您正在编写可能不太可能的OCI应用程序)决定是否以及如何调用这些API 。由于答案可能最终是特定于API的,您使用的API是什么,以及在发生错误时您的代码是什么样的(即JDBC,ODBC,OLE DB等)?

As an example, if I write a PL/SQL block with a misspelled variable name, SQL*Plus will report the line and column number of the error in addition to the error message. Many APIs, on the other hand, will just report the PLS-00201 error by default.

例如,如果我编写带有拼写错误的变量名的PL / SQL块,除了错误消息之外,SQL * Plus还将报告错误的行号和列号。另一方面,许多API默认只报告PLS-00201错误。

SQL> declare
  2    i integer;
  3  begin
  4    j := 1;
  5  end;
  6  /
  j := 1;
  *
ERROR at line 4:
ORA-06550: line 4, column 3:
PLS-00201: identifier 'J' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored

Similarly, if you execute a SQL statement with an invalid variable name, SQL*Plus will get the column and line position and put a * under the offending character, i.e.

同样,如果执行带有无效变量名的SQL语句,SQL * Plus将获取列和行位置并在违规字符下放置*,即

SQL> create table a( col1 number );

Table created.

SQL> insert into a( colN ) values ( 1 );
insert into a( colN ) values ( 1 )
               *
ERROR at line 1:
ORA-00904: "COLN": invalid identifier

Most PL/SQL IDE's (TOAD, SQL Developer, etc.) will do something similar by interrogating the appropriate OCI APIs under the covers. Precisely how this is done, however, will depend on the API.

大多数PL / SQL IDE(TOAD,SQL Developer等)都会通过查询相关的OCI API来做类似的事情。但是,具体如何完成这将取决于API。

#1