在 Oracle 存储过程中记录错误消息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1568166/
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
Log error messages in Oracle stored procedure
提问by David Mann
We plan to configure a stored procedure to run as a batch job daily using Oracle DBMS scheduler package. We would like to know what would be the best way to log an error message when there is an error occured. Is logging to a temporary table an option? or is there a better option. Thanks in advance.
我们计划使用 Oracle DBMS 调度程序包将存储过程配置为每天作为批处理作业运行。我们想知道在发生错误时记录错误消息的最佳方式是什么。记录到临时表是一种选择吗？或者有更好的选择。提前致谢。
采纳答案by dpbradley
You say that you don't have a lot of control over the DB environment to install logging packages - if this is the case then you'll be limited to querying the information in the dba_scheduler_job_run_details and dba_scheduler_job_log system views - you'll be able to see the history of executions here. Unhandled exceptions will show up in the ADDITIONAL_INFO column. If you need notification you can poll these views and generate email.
您说您对安装日志包的数据库环境没有太多控制权 - 如果是这种情况，那么您将仅限于查询 dba_scheduler_job_run_details 和 dba_scheduler_job_log 系统视图中的信息 - 您将能够在此处查看处决历史。未处理的异常将显示在 ADDITIONAL_INFO 列中。如果您需要通知，您可以轮询这些视图并生成电子邮件。
回答by David Mann
If you decide to roll your own logging and log into a table you might go the Autonomous Transactionroute.
如果您决定滚动自己的日志记录并登录到表中，您可能会走自治事务路线。
An Autonomous Transaction is a transaction that can be commited independently of the current transaction you are in.
自治事务是可以独立于您所在的当前事务提交的事务。
That way you can log and commit all the info you want to your log table independently of the success or failure of your stored procedure or batch process parent transaction.
这样您就可以独立于存储过程或批处理父事务的成功或失败记录并提交您想要的所有信息到您的日志表。
CREATE OR REPLACE PROCEDURE "SP_LOG" (
P_MESSAGE_TEXT VARCHAR2
) IS
pragma autonomous_transaction;
BEGIN
DBMS_OUTPUT.PUT_LINE(P_MESSAGE_TEXT);
INSERT INTO PROCESSING_LOG (
MESSAGE_DATE,
MESSAGE_TEXT
) VALUES (
SYSDATE,
P_MESSAGE_TEXT
);
COMMIT;
END;
/
Then if you call it like this, you can still get messages committed to your log table even if you have a failure and roll back your transaction:
然后，如果您这样称呼它，即使您遇到故障并回滚您的事务，您仍然可以将消息提交到您的日志表：
BEGIN
SP_LOG('Starting task 1 of 2');
... code for task 1 ...
SP_LOG('Starting task 2 of 2');
... code for task 2 ...
SP_LOG('Ending Tasks');
... determine success or failure of process and commit or rollback ...
ROLLBACK;
END;
/
You may want to tidy it up with exceptions that make sense for your code, but that is the general idea, the data written in the calls to SP_LOG persists, but the parent transaction can still be rolled back.
您可能想用对您的代码有意义的异常来整理它，但这是一般的想法，在对 SP_LOG 的调用中写入的数据仍然存在，但父事务仍然可以回滚。
回答by user151019
You could use log4plsql http://log4plsql.sourceforge.net/and change the choice later by configuration changes not code changes
您可以使用 log4plsql http://log4plsql.sourceforge.net/并稍后通过配置更改而不是代码更改来更改选择
The log4plsql page gives a list of various places it can log.
log4plsql 页面提供了它可以记录的各种位置的列表。
It also depends how applications and systems are monitored in your environment - if there is a standard way fir example a business I worked add used used irc for monitoring - then you might want a function that calls to that.
它还取决于如何在您的环境中监控应用程序和系统 - 如果有一个标准方法，例如我工作的企业添加使用 irc 进行监控 - 那么您可能需要一个调用它的函数。
回答by Henry Gao
that depends on how you will deal with errors: if you just need to be notified, the email is the best option; if you need to manually continue process the error, the table is good choice.
这取决于您将如何处理错误：如果您只需要收到通知，电子邮件是最佳选择；如果您需要手动继续处理错误，该表是不错的选择。
