Oracle 跳过数据损坏-有什么方法可以跳过PLSQL存储的Proc中的过去ORA-01427？

嗨，汤姆

我有一个存储的proc，它将视图的内容转储到一个表中。存储的proc只是使用 “INSERT in-select *” 来自语句来完成它的工作。
但是，在糟糕的一天，我们有一些数据损坏，导致ORA-01427。我正在谈论的这个存储的Proc是通过批处理过程调用的，这对我的客户来说非常重要。我了解，数据损坏非常严重，不容忽视。但是，有什么方法可以跳过过去的ORA-01427并在某些日志文件中将损坏的数据标记为 “损坏的数据”？

代码:

create or replace PROCEDURE "SP_CS_LT" (v_switch_cl IN VARCHAR2, v_switch_cs IN VARCHAR2, v_msg OUT VARCHAR2)
IS
v_tbl_name varchar2(200);
Begin
  IF v_switch_cl = 'Y' then
  v_tbl_name := 'CS_LST';
  Execute Immediate 'truncate table CS_LST';
  Execute Immediate 'insert into CS_LST (select distinct * from CS_LST_VW)';
  END IF;
  If V_Switch_Cs = 'Y' Then
  v_tbl_name := 'CS_SRCH';
  Execute Immediate 'truncate table CS_SRCH';
  Execute Immediate 'INSERT INTO CS_SRCH (SELECT DISTINCT * FROM CS_SRCH_VW)';
  END IF;
  v_msg := 'SUCCESS';
  commit;
exception
  when others then
    v_msg := 'FAILURE:  table - '|| v_tbl_name ||'   '|| SQLERRM;
END SP_CS_LT;

问题出在您的视图的查询中。在它的某个地方，你有一个标量子查询，它返回多个行。如果您可以找出视图的哪一列正在引发此异常，则可以将其从负载中排除。

例如，t2是t的子项。t中的第1行在t2中有1行。但是t中的第2行匹配t2中的两行:

create table t as
  select rownum x from dual connect by level <= 2;

create table t2 as
  select x, rownum y
  from   t, lateral (select * from dual connect by level <= x);

select * from t;

         X
----------
         1
         2

select * from t2;

         X          Y
---------- ----------
         1          1
         2          2
         2          3

因此，如果您尝试在select中作为子查询获取t的每一行的匹配t2值，则会出现错误:

select x, ( select y from t2 where t2.x = t.x) y from t;

ORA-01427: single-row subquery returns more than one row

弄清楚为什么会发生这种情况是检查这些子查询的视图以及一些反复试验的组合。

一旦确定了违规列，就可以编写代码来捕获异常，并根据需要将其替换为null/magic value/“MISSING DATA”:

create or replace view v as 
  select x, ( select y from t2 where t2.x = t.x) y from t;
  
create table t3 as
  select * from v where 1=2;
  
declare
  single_row_ex exception;
  pragma exception_init (single_row_ex, -01427);
begin
  insert into t3
    select * from v;
exception 
  when single_row_ex then 
    insert into t3
      select x, null y from v;
end;
/

select * from t3;

         X          Y
---------- ----------
         1           
         2

当然，更好的解决方案是修复视图，这样就不会发生这种情况!正确的解决方案取决于您的数据和要求。选项包括:

-从子查询中取最小值/最大值:

create or replace view v as 
  select x, ( select min(y) from t2 where t2.x = t.x) y from t;

select * from v;

         X          Y
---------- ----------
         1          1
         2          2

-将子查询移动为联接:

create or replace view v as 
  select t.x, y 
  from   t
  join   t2
  on     t2.x = t.x;

select * from v;

         X          Y
---------- ----------
         1          1
         2          2
         2          3

您需要与应用程序用户交谈，以确定正确的方法是什么。