SQL Oracle 调试技术
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3731918/
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
Oracle debugging techniques
提问by Nathan Spears
I don't know where error information goes when a trigger doesn't work correctly.
当触发器不能正常工作时，我不知道错误信息去哪里了。
My tool for writing triggers has been Oracle's Sql Developer tool, and my knowledge of how to debug it is pretty much nonexistent. What are some pointers for being able to find useful information about things happening "behind the scenes"? Also, are there better tools that Sql Developer for connecting, testing, debugging, etc?
我编写触发器的工具是 Oracle 的 Sql Developer 工具，我几乎不知道如何调试它。能够找到有关“幕后”发生的事情的有用信息有哪些提示？另外，Sql Developer 是否有更好的工具用于连接、测试、调试等？
My method so far has been to write something (a trigger, for instance), test it with single inserts/deletes, and then hope it works from them on. Are there better ways to make sure it's doing exactly what you want? For instance, with a select statement, is there anyway to see (in a debug state or something) each level of the select and how it whittles down results? Any tips greatly appreciated.
到目前为止，我的方法是写一些东西（例如一个触发器），用单个插入/删除来测试它，然后希望它在它们之后工作。有没有更好的方法来确保它完全按照您的意愿行事？例如，使用 select 语句，是否可以查看（在调试状态或其他状态下）选择的每个级别以及它如何减少结果？任何提示非常感谢。
回答by Gary Myers
Firstly, all code works correctly. It just doesn't do what you expected it to do.
首先，所有代码都可以正常工作。它只是没有做你期望它做的事情。
Secondly, "Don't start from here", or specifically don't use triggers. It is basically going to force switching to row-level processing if triggers are going to fire for each row. Better to actually put the logic in a stored procedure which you call. You've then got a start (where you validate inputs) and an end and a logic path all the way through. They are a lot easier to debug as you follow one path.
其次，“不要从这里开始”，或者特别是不要使用触发器。如果触发器要为每一行触发，它基本上会强制切换到行级处理。最好将逻辑实际放在您调用的存储过程中。然后你就有了一个开始（你验证输入的地方）和一个结束和一个逻辑路径。当您遵循一条路径时，它们会更容易调试。
Three, Never test for an error you don't know how to handle. If you don't catch it, it bubbles up to the client who gets an error report saying what went wrong (error message) and where (ie the error/call stack). If you try to catch it, you have to know what to do with it (and if you don't know the tendency is to ignore it - which is BAD).
三、永远不要测试你不知道如何处理的错误。如果你没有发现它，它就会冒泡到客户端，客户端会收到一个错误报告，说明出了什么问题（错误消息）和哪里（即错误/调用堆栈）。如果你试图抓住它，你必须知道如何处理它（如果你不知道忽略它的倾向——这是不好的）。
Finally, you can't readily see each 'layer' of a select. The explain plan will generally tell you how its going about things. v$session_longops MAY indicate what it is currently doing. The current wait event MAY give clues as to what table/block/row it is currently working on.
最后，您无法轻易看到选择的每个“层”。解释计划通常会告诉您事情的进展情况。v$session_longops 可以指示它当前在做什么。当前的等待事件可能会提供有关它当前正在处理的表/块/行的线索。
回答by Jeffrey Kemp
A rough-and-ready simple method if you must debug triggers is to use DBMS_OUTPUT.
如果您必须调试触发器，一个粗略的简单方法是使用DBMS_OUTPUT。
e.g.
例如
SQL> CREATE OR REPLACE TRIGGER mytrigger
BEFORE UPDATE ON mytable
FOR EACH ROW
...
BEGIN
DBMS_OUTPUT.put_line('mytrigger STARTING');
... do some logic ...
DBMS_OUTPUT.put_line('old=' || :OLD.mycolumn);
DBMS_OUTPUT.put_line('new=' || :NEW.mycolumn);
DBMS_OUTPUT.put_line('mytrigger FINISHED');
END;
/
SQL> SET SERVEROUT ON
SQL> UPDATE mytable SET mycolumn = mycolumn + 1;
2 rows updated.
mytrigger STARTING
old=10
new=11
mytrigger FINISHED
mytrigger STARTING
old=20
new=21
mytrigger FINISHED
回答by Adam
Application I use a program from Quest called TOAD available at www.quest.com/toad/toad-for-oracle.aspx.
应用程序 我使用 Quest 的一个名为 TOAD 的程序，可从 www.quest.com/toad/toad-for-oracle.aspx 获得。
As mentioned above, DBMS_OUTPUT is very handy. In your editor, make sure you enable the Output window.
如上所述，DBMS_OUTPUT 非常方便。在您的编辑器中，确保启用“输出”窗口。
PL/SQL works on "blocks" of code and you can catch it with an EXCEPTION keyword.
PL/SQL 处理代码“块”，您可以使用 EXCEPTION 关键字捕获它。
(Please forgive my formatting, not sure how to format for web)
（请原谅我的格式，不知道如何为网页设置格式）
DECLARE
C_DATE_FORMAT VARCHAR2(20) := 'DD-Mon-YYYY';
C_TIME_FORMAT VARCHAR2(20) := 'HH24:MI:SS';
C_NOT_IMPLEMENTED_CODE CONSTANT NUMBER(5) := -20200;
C_NOT_IMPLEMENTED_MESSAGE CONSTANT VARCHAR2(255) := 'Not implemented';
not_implemented EXCEPTION; -- user defined exception
BEGIN
--RAISE not_implemented; -- raise user defined exception
RAISE_APPLICATION_ERROR(C_NOT_IMPLEMENTED_CODE, C_NOT_IMPLEMENTED_MESSAGE); -- user defined exception
EXCEPTION -- exception block
WHEN not_implemented THEN -- catch not_implemented exception
DBMS_OUTPUT.PUT_LINE('Error: Not implemented');
WHEN OTHERS THEN -- catch all other exceptions
DBMS_OUTPUT.PUT_LINE('Error occured.');
DBMS_OUTPUT.PUT_LINE('Date: ' || TO_CHAR(SYSDATE, C_DATE_FORMAT));
DBMS_OUTPUT.PUT_LINE('Time: ' || TO_CHAR(SYSDATE, C_TIME_FORMAT));
DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('Error message: ' || SQLERRM); --deal with error
RAISE; -- raise to calling object
END;
回答by Rob van Laarhoven
SQL Developer has a nice PL/SQL debugger: http://www.packtpub.com/article/debugging-pl-sql-in-oracle-sql-developer
SQL Developer 有一个不错的 PL/SQL 调试器：http: //www.packtpub.com/article/debugging-pl-sql-in-oracle-sql-developer
回答by Hyman Huggins
There is also a good Oracle debuggertool in dbForge Studio for Oracle with step-by-step code execution, breakpoints, a call stack, watches, a variables evaluation mechanism for Oracle stored functions and procedures debugging automation.
在 dbForge Studio for Oracle 中还有一个很好的Oracle 调试器工具，具有分步代码执行、断点、调用堆栈、监视、Oracle 存储函数的变量评估机制和过程调试自动化。
