处理和记录ORA-06512错误代码

Question

我刚刚创建了一个error_log表来记录过程/包可能遇到的任何错误。错误日志表如下所示

CREATE TABLE APMS.ERROR_LOG
(
ORA_ERR_TMSP     TIMESTAMP(6)                 NOT NULL,
ORA_ERR_NUMBER   NUMBER(5)                    NOT NULL,
ORA_ERR_MSG      VARCHAR2(200 CHAR)           NOT NULL,
ORA_ERR_TXT      VARCHAR2(500 CHAR)           NOT NULL,
ORA_ERROR_OPTYP  CHAR(1 CHAR)                 NOT NULL,
PROGRAM_NAME     VARCHAR2(50 CHAR)            NOT NULL,
ORA_IN_OUT       VARCHAR2(500 CHAR)           NOT NULL
)

我创建了一个模拟表，通过使用过程在时间戳字段中插入一个字符串，故意导致ORA-06512错误。下面是将虚拟数据插入模拟表的过程，目的是诱导错误并将其记录到我的error_log表中。

create or replace procedure test_procedure as 

begin 
  insert into mockdata values ('data1','mockname','mockcity');
  commit;
exception
  when others then
    insert into error_log 
    values   
    (ora_err_tmsp,ora_err_number,ora_err_msg,ora_err_txt,ora_err_optyp,program_name,ora_in_out);
    values
    (current_timestamp,sqlcode,'sqlerrm', 'detailed information','i','test_procedure','i');
    commit;
end;
/

当我试图运行/编译它时，我会得到以下错误。

[Error] PLS-00103 (9: 1): PLS-00103: Encountered the symbol "VALUES" when expecting one of the following:

   ( begin case declare end exit for goto if loop mod null
   pragma raise return select update when while with
   <an

我是pl/sql的完全初学者，所以任何帮助都是非常感谢的。

Answer 1

在进入您的代码之前:如果您计划在编写的每个过程中编写这样的日志记录，那么也许您应该避免这样做，只需在需要的时候使用这个触发器并启用它:这个触发器在测试环境中可能很方便，或者如果您遇到了很大的麻烦，您需要收集在给定时间内发生的所有可能的错误：

     CREATE OR REPLACE TRIGGER log_all_errors  AFTER SERVERERROR ON DATABASE
     declare
        procedure log_error is
        pragma autonomous_transaction;
        begin                  
            INSERT INTO error_log
            VALUES (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.DATABASE_NAME, DBMS_UTILITY.FORMAT_ERROR_STACK);
            commit;
        end;

     BEGIN
         log_error;
     END log_all_errors ;

此触发器将记录系统中发生的所有错误。永久启用它并不是一个好主意:您可以在紧急情况下使用它进行一些故障排除，并且您可能希望调整其代码，只记录某些错误，但这是一个起点。如果您做了一些研究，您会发现甚至可以记录导致错误的语句的SQL文本。请记住，有些错误没有被此触发器捕获(我指的是NO_DATA_FOUND和TOO_MANY_ROWS错误)：在其正常的生命周期中，有太多的代码通常使用这些异常，因此甲骨文的人员决定不捕获这些错误。

现在让我们回到您的代码:正如其他人所指出的，您的方法与程序的正常执行并不是中立的：

1. 您正在记录错误，对，但您正在将错误隐藏到调用过程的程序中。这不是一个好主意:调用程序当然想知道发生了什么错误，它想发出一个“回滚”以取消以前的所有工作，而不是继续像没有什么错误发生。
2. 不仅您正在隐藏错误，而且在发生错误时也会发出commit :这很可能与您的调用程序在发生错误时所做的相反:调用方肯定会发出回滚。相反，您正在使所有部分工作永久化，直到错误发生为止。
3. 另外，请注意:即使在您的过程中没有发生错误时，也存在上述不需要提交的问题:您也是在未引发错误的情况下提交。这意味着，如果调用程序在调用您的过程之后出现了一些问题，它将无法在调用代码之前回滚它所做的任何事情。 在一个过程中提交或回滚通常是一种不好的做法，除非您是，而不是，因为这样的过程永远不会作为更大事务的一部分被调用(例如:如果您的过程是数据库作业的主体，提交是可以的)。

那么:如何在不干扰调用程序的情况下编写错误日志？您可以通过在一个专用的自治事务“过程”中写入日志来完成这一任务:在标记为“可信事务”的过程中所做的任何操作都在它自己的单独事务中运行:您提交或回滚只在它内部发生的事情。

(如您所见，我在上面的触发器中也使用了一个自治事务)

如果您的代码是这样编写的，那么您的代码对调用程序来说就更“亲切”了：

     CREATE OR REPLACE PROCEDURE test_procedure AS

        procedure write_error_log (errcode number, errstr varchar2) is
        pragma autonomous_transaction; 
        -- whatever we do in this procedure stays in its own new private transaction
        begin                         
              INSERT INTO error_log
                   (ora_err_tmsp,
                          ora_err_number,
                          ora_err_msg,
                          ora_err_txt,
                          ora_err_optyp,
                          program_name,
                          ora_in_out)
              values (CURRENT_TIMESTAMP,
                      errcode,
                      errstr,
                      'detailed information',
                      'i',
                      'test_procedure',
                      'i');
              COMMIT;  -- this commit does not interfere with the caller's transaction.
        end write_error_log;

     BEGIN
        INSERT INTO mockdata
            VALUES ('data1', 'mockname', 'mockcity');
        --here you were committing: you'd better not do it:
        --  you are making impossible for the calling program to roll back 
        -- COMMIT; 
      exception when others then             
        write_error_log(sqlcode,sqlerrm);
        raise; -- you should NOT hide the exception to the caller program, so you'd better re-raise it!
     END test_procedure;

Answer 2

试试这个：

CREATE OR REPLACE PROCEDURE test_procedure
AS
BEGIN
   INSERT INTO mockdata
       VALUES ('data1', 'mockname', 'mockcity');

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      INSERT INTO error_log
           (ora_err_tmsp,
                  ora_err_number,
                  ora_err_msg,
                  ora_err_txt,
                  ora_err_optyp,
                  program_name,
                  ora_in_out)
      values (CURRENT_TIMESTAMP,
              SQLCODE,
              sqlerrm,
              'detailed information',
              'i',
              'test_procedure',
              'i');
      COMMIT;
END;
/