Oracle 使用日志错误记录到

我需要将记录 (或任何其他DML) 插入到表中-但是如果遇到错误记录，则必须继续插入并记录错误。

从逻辑上讲，我想到了使用日志错误到表中。

我有几个关于这个的问题-

是否可以使用集合或临时表代替错误日志表 (使用DBMS_ERRLOG.create_error_log或手动表创建)

日志错误是 “在错误记录的情况下继续插入并为错误记录返回日志信息” 方案的唯一方法吗？

3.如果在DML语句中使用了返回进入子句，将错误记录到仍然有效吗？

谢谢!

您可以将错误日志创建为全局临时表。但是您必须使用 “在提交保留行” 选项执行此操作:

create table t (
  c1 int primary key, c2 varchar(5)
);

create global temporary table err$_t ( 
  ora_err_number$ number, 
  ora_err_mesg$ varchar2(2000), 
  ora_err_rowid$ urowid (4000), 
  ora_err_optyp$ varchar2(2), 
  ora_err_tag$ varchar2(2000), 
  c1 varchar2(4000), 
  c2 varchar2(4000)
);

insert into t 
  with rws as (
    select level x from dual
    connect by level <= 10
  )
    select x, lpad ( 'x', x, 'x' ) 
    from   rws
  log errors into err$_t
  reject limit unlimited;

select c1, c2 from err$_t;

no rows selected
rollback;

truncate table err$_t;
drop table err$_t 
  cascade constraints purge;
  
create global temporary table err$_t ( 
  ora_err_number$ number, 
  ora_err_mesg$ varchar2(2000), 
  ora_err_rowid$ urowid (4000), 
  ora_err_optyp$ varchar2(2), 
  ora_err_tag$ varchar2(2000), 
  c1 varchar2(4000), 
  c2 varchar2(4000)
) on commit preserve rows;

insert into t 
  with rws as (
    select level x from dual
    connect by level <= 10
  )
    select x, lpad ( 'x', x, 'x' ) 
    from   rws
  log errors into err$_t
  reject limit unlimited;

select * from t;

C1   C2      
    1 x        
    2 xx       
    3 xxx      
    4 xxxx     
    5 xxxxx  

select c1, c2 from err$_t;

C1   C2           
6     xxxxxx        
7     xxxxxxx       
8     xxxxxxxx      
9     xxxxxxxxx     
10    xxxxxxxxxx    

rollback;

在PL/SQL中使用批量处理时，还可以使用FORALL的SAVE EXCEPTIONS来收集失败行的数组。

declare
  cursor cur is 
    with rws as (
      select level x from dual
      connect by level <= 10
    )
      select x c1, lpad ( 'x', x, 'x' ) c2
      from   rws;
  
  type cur_rec is table of cur%rowtype
    index by pls_integer;
    
  recs cur_rec;
begin
  open cur;
  fetch cur bulk collect into recs;
  
  forall i in 1 .. recs.count 
    save exceptions
    insert into t 
    values recs( i );
    
  close cur;
end;
/

ORA-24381: error(s) in array DML

select * from t;

C1   C2      
    1 x        
    2 xx       
    3 xxx      
    4 xxxx     
    5 xxxxx    

要查看错误是什么，您需要在异常块中处理ORA-24381。在以下位置阅读有关此的更多信息:

https://livesql.oracle.com/apex/livesql/file/content_CMSE3VBYFCO9IX3PGE4S5C1Q8.html
https://blogs.oracle.com/oraclemagazine/bulk-processing-with-bulk-collect-and-forall

还有另一种方法可以继续过去的错误。忽略 _ row_on_dupkey_index提示。这会默默地绕过具有主键或唯一键冲突的任何行:

insert into t values ( 1, 'new' );

ORA-00001: unique constraint (CHRIS.SYS_C008328) violated

insert /*+ ignore_row_on_dupkey_index ( t ( c1 ) ) */into t 
values ( 1, 'new' );

0 rows inserted.

但是与日志错误/保存异常不同，您没有被忽略的行的记录。之后你需要做比较，看看这些是什么。

Will LOG ERRORS INTO still work if the RETURNING INTO clause is used in the DML statement?

是的

declare
  val int;
begin
  insert into t values ( 99, 'fine' )
  returning c1 into val
  log errors into err$_t;
  
  dbms_output.put_line ( 'Added ' || val );
end;
/

Added 99