oracle - 获取 ORA-01422: 调用过程时精确提取返回的行数超过了请求的行数

Question

CREATE TABLE cmb_staging (
    e_id               NUMBER(10),
    e_name             VARCHAR2(30),
    e_loc              VARCHAR2(30),
    validation_status  VARCHAR2(30),
    validation_result  VARCHAR2(30)
);

insert into cmb_staging values(1,'A','AA',null,null);
insert into cmb_staging values(1,'B','BB',null,null);
insert into cmb_staging values(2,'C','CC',null,null);
insert into cmb_staging values(2,'D','DD',null,null);
insert into cmb_staging values(3,'A','AA',null,null);

CREATE TABLE cmb_target (
    e_id    NUMBER(10),
    e_name  VARCHAR2(30),
    e_loc   VARCHAR2(30)
);

CREATE TABLE cmb_reject (
    e_id               NUMBER(10),
    e_name             VARCHAR2(30),
    e_loc              VARCHAR2(30),
    validation_status  VARCHAR2(30),
    validation_result  VARCHAR2(30)
);

CREATE TABLE SUMMARY_TAB
   (    TOT_RECORDS NUMBER(10,0), 
    SUCCESS_RECORDS NUMBER(10,0), 
    FAILED_RECORDS NUMBER(10,0), 
    PROCESS_STATUS VARCHAR2(30)
   );

程序 ：

create or replace procedure sp_dup_rec(ov_err_msg OUT varchar2)
    is
      lv_succ_rec number(30);
      lv_fail_rec number(30);
      lv_count number(30);
    begin
      lv_succ_rec := 0;
      lv_fail_rec := 0;

      UPDATE cmb_staging
      SET   validation_status = 'Fail',
            validation_result    = CASE
                                WHEN e_id IS NULL
                                THEN 'Id is not present'
                                ELSE 'Id is longer than expected'
                                END
      WHERE e_id is null
      OR    LENGTH(e_id) > 4;

--If there are duplicates id then it should go into cmb_reject table
select e_id into lv_count from cmb_staging;
if lv_count < 1 then
      MERGE INTO cmb_target t
        USING (SELECT e_id,
             e_name,
             e_loc
      FROM   cmb_staging
      WHERE  validation_status IS NULL) S
        ON (t.e_id = S.e_id)

            WHEN MATCHED THEN UPDATE SET 
                t.e_name = s.e_name,
                t.e_loc = s.e_loc


            WHEN NOT MATCHED THEN INSERT (t.e_id,t.e_name,t.e_loc)
                VALUES (s.e_id,s.e_name,s.e_loc);

      lv_succ_rec := SQL%ROWCOUNT;
else
      insert into cmb_reject
      select s.*
      from   cmb_staging s
      WHERE  validation_status = 'Fail';

      lv_fail_rec := SQL%ROWCOUNT;
end if;

      dbms_output.put_line('Inserting into Summary table');
      insert into summary_tab(
        tot_records,
        success_records,
        failed_records
      ) values (
        lv_succ_rec + lv_fail_rec,
        lv_succ_rec,
        lv_fail_rec
      );

      COMMIT;
      ov_err_msg := 'Procedure completed succesfully';
    EXCEPTION
      WHEN OTHERS THEN
        ov_err_msg := 'Procedure end up with errors'|| sqlerrm;
        ROLLBACK;
    END sp_dup_rec;

调用过程：

set serveroutput on;
declare
    v_err_msg varchar2(100);
begin
    sp_dup_rec(v_err_msg);
    dbms_output.put_line(v_err_msg);
end;

嗨团队，我在调用程序时收到 ora-01422 错误。基本上，我想将重复记录插入 cmb_reject 选项卡，因为合并语句将失败，如果我只使用合并，我将收到 ora - 30296 错误。所以，我写了 if 条件，它将获取计数，如果计数更多，则将插入 cmb_reject 选项卡

标签： oracleplsql

Answer 1

The error stack should tell you the line that is throwing the error. My guess is that the line is

select e_id 
  into lv_count 
  from cmb_staging;

because that query doesn't make sense. If there are multiple rows in cmb_staging, the query will throw the ORA-01422 error because you can't put multiple rows in a scalar variable. It seems highly probable that you'd at least want your query to do a count. And most likely with some sort of predicate. Something like

select count(*)
  into lv_count 
  from cmb_staging;

would avoid the error. But that likely doesn't make sense given your comments where you say "it should go into the cmb_reject table" which implies that there is a singular object. This change would cause lv_count > 0 whenever there were any rows in cmb_staging and it seems unlikely that you want to always reject rows.

My rough guess is that you really mean "two rows with the same e_id" when you say "duplicate". If that's right

select e_id, count(*)
  from cmb_staging
 group by e_id
having count(*) > 1

will show you the duplicate e_id values. You could then

  insert into cmb_reject
  select s.*
  from   cmb_staging s
  where  e_id in (select s2.e_id
                    from cmb_staging s2
                   group by s2.e_id
                  having count(*) > 1);

If you don't really care about performance (I'm assuming based on the questions you've asked that you're just learning PL/SQL and you're not trying to manage a multi-TB data warehouse load) and just want to process the records in a loop

for stg in (select s.*,
                   count(*) over (partition by s.e_id) cnt
              from cmb_staging s)
loop
  if( stg.cnt > 1 )
  then
    insert into cmb_reject( e_id, e_name, e_loc )
      values( stg.e_id, stg.e_name, stg.e_loc );
    l_fail_cnt := l_fail_cnt + 1;
  else 
    merge into cmb_target tgt
      using( select stg.e_id e_id,
                    stg.e_name e_name,
                    stg.e_loc e_loc
               from dual ) src
         on( src.e_id = tgt.e_id )
       when not matched 
       then 
         insert( e_id, e_name, e_loc )
           values( src.e_id, src.e_name, src.e_loc )
       when matched
       then
         update set e_name = src.e_name,
                    e_loc  = src.e_loc;
      
    l_success_cnt := l_success_cnt + 1;
  end if;
end loop;