oracle中的错误/异常处理

Question

我想为以下场景制定一个程序。

我有一个源、一个目标和一个错误表。目标表和错误表具有源表中存在的所有字段。但错误表的所有字段的数据类型都是varchar。错误表没有完整性、外键和其他约束。 错误表还有两个字段：错误号和错误消息。

现在，当执行过程时，如果在将任何记录插入目标时出现错误，则该记录应移至错误表。此外，数据库错误代码和错误消息应记录在错误表字段中，如上所述。

我怎样才能开发这样的程序？

表架构示例：

source table  
    src(id number 
        ,name varchar2(20)  
        , ... )

target table  
    tgt(id number 
        ,name varchar2(20) not null 
        , ... )

error table  
    err (id varchar2(255) 
          ,name  varchar2(255)
          , ... 
          , errno varchar2(255)
          , errmsg varchar2(255))

Answer 1

您看过 Oracle 自己的错误日志记录功能吗？

http://download.oracle.com/文档/cd/B19306_01/server.102/b14231/tables.htm#ADMIN10261

Answer 2

执行此操作的过程可能如下所示：

procedure ins_tgt(p_id in number, p_name in varchar2, ...) is
  v_errno number; v_errmsg varchar2(2000);
begin
  insert into tgt(id, name, ...) values (p_id, p_name, ...);
exception
  when others then
    /* copy sqlcode and sqlerrm into variables since they can't be used directly in a SQL statement */
    v_errno := sqlcode;
    v_errmsg := sqlerrm;
    insert into err(id, name, errno, errmsg) values (p_id, p_name, v_errno, v_errmsg);
end;


procedure copy_src_tgt is
begin
  for s in (select * from src) loop
    ins_tgt(s.id, s.name, ...);
  end loop;
end;

但这似乎是一种将数据从一个表复制到另一个表的可怕低效方法......

Answer 3

CREATE OR REPLACE PACKAGE BODY foo_dml IS

    PROCEDURE log_err (
        p_sqlcode IN NUMBER,
        p_sqlerrm IN VARCHAR2,
        p_src     IN foo%ROWTYPE
    ) IS
        -- inserts the input row to the err log
    BEGIN
        INSERT INTO err (
            errno,
            errmsg,
            ID,
            NAME,
            ...
        ) VALUES (
            p_sqlcode,
            p_sqlerrm,
            p_src.id,
            p_src.name,
            ...
        );
    END;

    PROCEDURE copy_to_tgt (
        p_src IN foo%ROWTYPE
    ) IS
        -- copies the input row to the tgt table
    BEGIN
        INSERT INTO
            tgt
        VALUES
            p_src;
    EXCEPTION
        WHEN OTHERS THEN
            log_err( SQLCODE, SQLERRM, p_src );
    END;

END;
/