如何在 oracle 10g 中为存储过程创建错误日志？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4171955/
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 create error log for stored procedure in oracle 10g?
提问by puspanjali
I need an example of creating error log file for stored procedure in oracle. please give me an example with table creation and stored procedure creation and error log creation.
我需要一个在 oracle 中为存储过程创建错误日志文件的示例。请给我一个表创建和存储过程创建以及错误日志创建的示例。
Thanks in advance
提前致谢
EDIT(relevant info from other question)
编辑（来自其他问题的相关信息）
Suppose there is a stored procedure. When I am executing that stored procedure, some expected error/exception may occur, so I need to create an error log table in which all the errors will automatically be store whenever I will execute the stored procedure.
假设有一个存储过程。当我执行该存储过程时，可能会发生一些预期的错误/异常，因此我需要创建一个错误日志表，每当我执行该存储过程时，所有错误都会自动存储在其中。
For example, if there is some column which does not allow null values, but the user is entering null values, then that error should be generated and it should stored in the error log table.
例如，如果某些列不允许空值，但用户输入空值，则应生成该错误并将其存储在错误日志表中。
回答by Jeffrey Kemp
You haven't really given a lot of detail about your requirements. Here is a simple error log table and a procedure to log error messages into it:
您还没有真正详细说明您的要求。这是一个简单的错误日志表和一个将错误消息记录到其中的过程：
CREATE TABLE error_log (ts TIMESTAMP NOT NULL, msg VARCHAR2(4000));
CREATE PROCEDURE log_error (msg IN VARCHAR2) IS
BEGIN
INSERT INTO error_log (ts, msg)
VALUES (SYSTIMESTAMP, SUBSTR(insert_log.msg, 1, 4000));
END log_error;
You might or might not need it to be an autonomous transaction. That would depend on whether you want the log to record errors from procedures that rollback their changes.
您可能需要也可能不需要它是一个自治事务。这取决于您是否希望日志记录回滚其更改的过程中的错误。
Typically, this will be implemented in a more generic logging system which would log not only errors, but warnings and debug info too.
通常，这将在更通用的日志系统中实现，该系统不仅会记录错误，还会记录警告和调试信息。
If you want a DML statement (insert/update/delete) to log an error for each row (instead of just failing on the first row that errors), you can use the LOG ERRORSclause - instead of the statement failing, the statement will succeed, and the rows that were not inserted/updated/deleted will be written to the error log table you specify, along with the error code and error message applicable. Refer to the link provided by vettipayyan.
如果您希望 DML 语句（插入/更新/删除）为每一行记录一个错误（而不是仅仅在第一行失败），您可以使用该LOG ERRORS子句 - 而不是该语句失败，该语句将成功，并且未插入/更新/删除的行将与适用的错误代码和错误消息一起写入您指定的错误日志表。请参阅 vettipayyan 提供的链接。
If you want all exceptions that are raised within a procedure to be logged, you can catch them with WHEN OTHERS:
如果您希望记录过程中引发的所有异常，您可以使用 WHEN OTHERS 捕获它们：
BEGIN
-- your code here
EXCEPTION
WHEN OTHERS THEN
log_error(DBMS_UTILITY.format_error_stack);
log_error(DBMS_UTILITY.format_error_backtrace);
RAISE;
END;
回答by vettipayyan
Here's the page with code samles:
DML ErrorLogging
这是带有代码示例的页面：
DML ErrorLogging
