oracle PL/SQL 出错时如何找出行号、过程名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2802182/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find out line number, procedure name in PL/SQL in case of an error
提问by bhagwat
I am using a D2k 6i form and getting the error on form from stored database(oracle9i) procedure ORA-00001:Unique constraint(.) violated but i m not able to trace out from which procedure it is coming. can anybody help me regarding this
我正在使用 D2k 6i 表单并从存储数据库 (oracle9i) 程序 ORA-00001:Unique constraint(.) 中获取表单错误，但我无法追踪它来自哪个程序。有人可以帮我解决这个问题吗
回答by akf
For posterity, here is the solution the OP found:
对于后代，这是 OP 找到的解决方案：
ok in D2k forms there is an ON-ERROR trigger where you can use the function DBMS_ERROR_TEXT to get the procedure,package name line number of the statement from where the error is coming
好的，在 D2k 表单中，有一个 ON-ERROR 触发器，您可以在其中使用函数 DBMS_ERROR_TEXT 来获取过程、程序包名称、错误所在语句的行号
回答by Greg Burghardt
I've come across this pattern after much research, head banging and gnashing of teeth:
经过大量研究，我遇到了这种模式，头部撞击和咬牙切齿：
CREATE OR REPLACE PACKAGE BODY my_schema.package_name
IS
PROCEDURE foo
IS
BEGIN
-- Call stored procedures/functions that throw unhandled exceptions
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR! - '
|| DBMS_UTILITY.FORMAT_ERROR_STACK
|| DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END foo;
END;
The DBMS_UTILITY.FORMAT_ERROR_STACKfunction seems to give the error code and message, and DBMS_UTILITY.FORMAT_ERROR_BACKTRACEseems to give an honest to goodness stack trace, complete with line numbers and stored procedure names in Oracle 10g at least.
该DBMS_UTILITY.FORMAT_ERROR_STACK函数似乎给出了错误代码和消息，并且DBMS_UTILITY.FORMAT_ERROR_BACKTRACE似乎给出了一个诚实的堆栈跟踪，至少在 Oracle 10g 中包含行号和存储过程名称。
I'm not sure if those functions are available in Oracle 9i. I couldn't find much information about this sort of thing even for Oracle 10g, so I thought I would at least post this answer since 9i is quite old (and so it 10g for that matter).
我不确定这些功能是否在 Oracle 9i 中可用。即使对于 Oracle 10g，我也找不到关于这类事情的太多信息，所以我想我至少会发布这个答案，因为 9i 已经很老了（因此它是 10g）。
回答by akf
Posting your exception with your question would give us a better idea of what you are confronted with.
将您的异常与您的问题一起发布将使我们更好地了解您所面临的问题。
Usually an exception will tell you the package and the line number within the error message. From that, you can query the USER_SOURCE table:
通常，异常会在错误消息中告诉您包和行号。从中，您可以查询 USER_SOURCE 表：
SELECT text
FROM user_source
WHERE type = 'PACKAGE BODY'
AND name = 'myProcName'
AND line = [the line number];
It might be useful to know more about the context within which the error was triggered. For that you can use the `BETWEEN' operator:
了解更多有关触发错误的上下文可能很有用。为此，您可以使用“BETWEEN”运算符：
SELECT text
FROM user_source
WHERE type = 'PACKAGE BODY'
AND name = 'myProcName'
AND line BETWEEN [the line number - 5] AND [the line number +5];
