Oracle PL/SQL-如何在异常子句中的单独表中捕获不良数据

Question

以下是代码

declare
   cursor c_data
   is
      select * from test_product_u;
begin
   for i in c_data
   loop   
      insert into test_product_u_final
                (PRODUCT_NO, CREATED_DATE, DATE_FORMAT) 
         values (i. PRODUCT_NO, i.CREATED_DATE,i.DATE_FORMAT);
   end loop;
exception when others then
---->'I want to catch the bad data here ? What are the options apart from the sqlerror message I want the data itself possibly in a VARCHAR column'<----
end;

1. 我想在异常条款中捕获不良数据吗？除了获取SQLERROR消息外，还有哪些选项我可能希望数据本身可能在Varchar列中？这是可能的。

2. DW负载通常会发生什么用于捕获无效数据？

Answer 1

根据代码示例，您只需在单独的错误表中将oper tear端端端端登录登录错误即可。下面我说明了示例。希望这会有所帮助。

DECLARE
  CURSOR c_data
  IS
    SELECT * FROM test_product_u;
BEGIN
  FOR i IN c_data
  LOOP
    BEGIN
      INSERT
      INTO test_product_u_final
        (
          PRODUCT_NO,
          CREATED_DATE,
          DATE_FORMAT
        )
        VALUES
        (
          i. PRODUCT_NO,
          i.CREATED_DATE,
          i.DATE_FORMAT
        );
    EXCEPTION
    WHEN OTHERS THEN
      INSERT
      INTO bad_data_tab VALUES
        (
          i.product_no
          ||';'
          ||i.created_date
          ||';'
          ||i.date_format
        );
    END;
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20009,SQLERRM,TRUE);
END;

Answer 2

从根本上讲，采用的方法是尽可能多地使用基于集合的处理，这将是（字面意思）100倍，而行逐行方法的速度更快。大多数数据库验证都可以使用基于SET SQL的功率完成。此外，如果您具有硬件（CPU和IO带宽），则很容易并行化以获得更多的性能。大多数DW负载采用"瀑布"方法，从表到表进行加载，直到数据最终干净为止。以您的一个数据检查为例，而不是null，可以使用多桌插入来编写类似的内容。

insert /*+ APPEND */ all
when ( col is null ) 
then
into table product_error_1 ( PRODUCT_NO, CREATED_DATE, DATE_FORMAT) values (PRODUCT_NO, CREATED_DATE, DATE_FORMAT )
else
into table product_stage_2 ( PRODUCT_NO, CREATED_DATE, DATE_FORMAT ) values ( PRODUCT_NO, CREATED_DATE, DATE_FORMAT )
select * from test_product_u
/

Answer 3

create table test_product_u
(product_no varchar2(1024),
created_date varchar2(1024),
date_format varchar2(1024) default 'yyyy-mon-dd');
create table test_product_u_final
(product_no varchar2(1024),
created_date date not null,
date_format varchar2(1024) default 'yyyy-mon-dd');
insert into test_product_u(product_no, created_date)
values('A', '2017-mar-02');
insert into test_product_u(product_no, created_date)
values('B', null);
insert into test_product_u(product_no, created_date)
values('C', '28-february-2017');
commit;

方法1：

begin
  dbms_errlog.create_error_log('TEST_PRODUCT_U_FINAL', 'TEST_PRODUCT_U_FINAL$ERR') ;
end;
declare
  cursor c_data is
    select * from test_product_u;
begin
  for i in c_data loop
    insert into test_product_u_final
      (product_no, created_date, date_format)
    values
      (i. product_no, i.created_date, i.date_format) log errors into test_product_u_final$err
      ('INSERT') reject limit unlimited;
  end loop;
end;

方法2：

declare
  cursor c_data is
    select * from test_product_u;
begin
  for i in c_data loop
    begin
      insert into test_product_u_final
        (product_no, created_date, date_format)
      values
        (i. product_no, i.created_date, i.date_format);
    exception
      when others then
        dbms_output.put_line('========== Exception (Start) ===============');
        dbms_output.put_line('Product: ' || i.product_no);
        dbms_output.put_line('Created Date: ' || i.created_date);
        dbms_output.put_line('Date Format: ' || i.date_format);
        dbms_output.put_line('Error Code: ' || sqlcode);
        dbms_output.put_line('Error Text: ' || sqlerrm);
        dbms_output.put_line('Error Trace: ' || dbms_utility.format_error_backtrace);
        dbms_output.put_line('========== Exception (End) ===============');
    end;
  end loop;
end;

和输出：

  ========== Exception (Start) ===============
  Product: A
  Created Date: 2017-mar-02
  Date Format: yyyy-mon-dd
  Error Code: -1861
  Error Text: ORA-01861: literal does not match format string
  Error Trace: ORA-06512: at line 7
  ========== Exception (End) ===============
  ========== Exception (Start) ===============
  Product: B
  Created Date: 
  Date Format: yyyy-mon-dd
  Error Code: -1400
  Error Text: ORA-01400: cannot insert NULL into ("FCI"."TEST_PRODUCT_U_FINAL"."CREATED_DATE")
  Error Trace: ORA-06512: at line 7
  ========== Exception (End) ===============