oracle PL/SQL 日志记录 - 如何控制？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1232246/
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
PL/SQL Logging - How to control?
提问by Ian Carpenter
I am looking to introduce a logging framework into our existing Oracle application to replace the use of DBMS_OUTPUT.
我希望在我们现有的 Oracle 应用程序中引入一个日志框架来取代 DBMS_OUTPUT 的使用。
The framework will be used primarly to aid debugging and would detail such things as starting x procedure, details of parameters, ending procedure x etc. It should also have the functionality to be turned on for all or just one program unit, various levels of trace in fact what is pretty much standard logging functionality.
该框架将主要用于辅助调试，并详细说明诸如启动 x 过程、参数详细信息、结束过程 x 等内容。它还应该具有为所有或仅一个程序单元打开的功能、各种级别的跟踪事实上，什么是非常标准的日志记录功能。
Implementing these requirements should be relatively straightforward, however where I would like your help is how best to turn this functionality off and on. What I am trying to achieve is the smallest possible performance hit when the trace is turned off. Which hopefully should be most of the time!
实现这些要求应该相对简单，但是我希望您的帮助是如何最好地关闭和打开此功能。我试图实现的是关闭跟踪时尽可能小的性能损失。希望大多数时候应该是这样！
As the application is using 10g release 2, I initially I liked the look of wrapping the logging mechanism inside conditional compilation so that logging framework is not even visible during normal operation. Unfortunately I have had to grudgingly abandon this idea as most of the application is built using stand-a-lone procedures & functions so turning on a logging functionality could potentially invalidate a lot of code.
由于应用程序使用 10g 第 2 版，我最初喜欢将日志记录机制包装在条件编译中的外观，这样日志记录框架在正常操作期间甚至不可见。不幸的是，我不得不勉强放弃这个想法，因为大多数应用程序都是使用独立的过程和函数构建的，因此打开日志记录功能可能会使许多代码无效。
I have had look a several existing opensource and other's frameworks\functionality for inspiration:
我已经查看了几个现有的开源和其他框架\功能以获得灵感：
log4plsql (http://log4plsql.sourceforge.net/)
log4plsql ( http://log4plsql.sourceforge.net/)
APC's review hereespecially under acceptable impact gives me concerns.
OraLog project (http://oralog.sourceforge.net)
OraLog 项目 ( http://oralog.sourceforge.net)
No updates since 2007
自 2007 年以来没有更新
PL/VISION(here)
PL/视觉（这里）
Looks quite old, no changes since Oracle 8i?
看起来很旧，自Oracle 8i 以来没有变化？
Ask Tom Instrumentation(here)
询问 Tom Instrumentation（此处）
Update 01/04/2014Tom Kytenow recommends Tyler Muth's Logger
更新 01/04/2014 Tom Kyte现在推荐 Tyler Muth's Logger
I would be really interested to hear your experiences if you have introduced some form of logging into your Oracle application, how you implemented it and especially how you control it.
如果您在 Oracle 应用程序中引入了某种形式的登录，以及您如何实施它，尤其是如何控制它，我真的很想听听您的经验。
采纳答案by dpbradley
You mentioned discarding the idea of conditional compilation because of potential cascading invalidations - there is an approach that is somewhat similar if you're willing to touch the PL/SQL source where logging/tracing is needed that doesn't involve recompilation to enable.
您提到由于潜在的级联失效而放弃了条件编译的想法 - 如果您愿意接触 PL/SQL 源，其中需要记录/跟踪而不涉及重新编译来启用，那么有一种方法有点类似。
You can still add a name/value pair of your own choosing to PLSQL_CCFLAGS and have your application code do a relatively lightweight query of v$parameter to determine if logging is "turned on". The crudest implementation would be one name/value pair, but you could extend this to have different pairs that would be module-specific so logging could be turned on with a finer granularity.
您仍然可以将您自己选择的名称/值对添加到 PLSQL_CCFLAGS，并让您的应用程序代码对 v$parameter 执行相对轻量级的查询，以确定日志记录是否“打开”。最粗略的实现将是一个名称/值对，但您可以将其扩展为具有特定于模块的不同对，因此可以以更精细的粒度打开日志记录。
[Edit] Here's a very simple example in response to your comment/request - you'll obviously want to be more sophisticated in parsing the PLSQL_CCFLAGS string in case it has other existing info, perhaps wrap into a function, etc.:
[编辑] 这是响应您的评论/请求的一个非常简单的示例 - 您显然希望在解析 PLSQL_CCFLAGS 字符串时更加复杂，以防它有其他现有信息，也许包装成函数等：
create or replace procedure ianc_cc
is
cc_flag_val varchar2(4000);
begin
-- need direct select grant on v_$parameter for this...
select value into cc_flag_val
from v$parameter where name = 'plsql_ccflags';
if (cc_flag_val = 'custom_logging:true') then
dbms_output.put_line('custom logging is on');
else
dbms_output.put_line('custom logging is off');
end if;
end;
/
Now, as a user privileged to issue ALTER SYSTEM:
现在，作为有权发出 ALTER SYSTEM 的用户：
ALTER SYSTEM set PLSQL_CCFLAGS='custom_logging:true';
更改系统设置 PLSQL_CCFLAGS='custom_logging:true';
and toggle back by:
并通过以下方式切换回来：
ALTER SYSTEM set PLSQL_CCFLAGS='';
更改系统设置 PLSQL_CCFLAGS='';
回答by Patrick
Reviewing the same problem, and found the following project which seems to be still be active, https://github.com/tmuth/Logger---A-PL-SQL-Logging-Utility
查看相同的问题，发现以下项目似乎仍然处于活动状态， https://github.com/tmuth/Logger---A-PL-SQL-Logging-Utility
回答by Stephen ODonnell
In our application, we make heavy use of Ask Tom's debug.f instrumentation. One thing I quickly noticed was that the 'debugtab' was getting queried way too much to see if logging was on or not for every single log message. I hacked a change into it to only check the table once every 100 log messages and now it works pretty well.
在我们的应用程序中，我们大量使用了 Ask Tom 的 debug.f 工具。我很快注意到的一件事是，'debugtab' 被查询得太多，无法查看每条日志消息的日志记录是否打开。我对它进行了更改，每 100 条日志消息只检查一次表，现在它运行良好。
My point is to try and avoid checking a table for each log message to see whether it should be output or not. Often you want to turn logging on in the middle of a long running process, so it's important you can do that. In my case, I decided I could live with waiting a few seconds until 100 logging calls had gone past before it actually noticed logging was turned on.
我的观点是尽量避免检查每个日志消息的表，以查看是否应该输出。通常，您希望在长时间运行的过程中打开日志记录，因此您可以这样做很重要。就我而言，我决定我可以忍受等待几秒钟，直到 100 个日志记录调用过去，然后它才真正注意到日志记录已打开。
回答by Rigved
Wouldn't it be easier to setup a context and add a name value pair to it? You can change the value in the context using a trigger on your debugtab table.
设置上下文并向其添加名称值对不是更容易吗？您可以使用 debugtab 表上的触发器更改上下文中的值。
