am getting records inserted only into target_tab but there are 3 records that are failing and should-CodePudding

create table staging_tab
(e_id number(10),
e_name varchar2(30),
e_loc varchar2(30),
validation_status varchar2(30),
validation_msg varchar2(30));

insert into staging_tab values(1,'A','AA',null,null);
insert into staging_tab values(2,'B','BB',null,null);
insert into staging_tab values(3,'C','CC',null,null);
insert into staging_tab values(null,'D','DD',null,null);
insert into staging_tab values(null,'E','EE',null,null);
insert into staging_tab values(null,'F','GG',null,null);

create table target_tab
(e_id number(10),
e_name varchar2(30),
e_loc varchar2(30));

create table reject_tab
(e_id number(10),
e_name varchar2(30),
e_loc varchar2(30),
validation_status varchar2(30),
validation_msg varchar2(30));

create table summary_tab
(tot_records number(10),
success_records number(10),
failed_records number(10)
);

Stored Procedure 

   create or replace procedure sp_stage_target_rej(ov_err_msg OUT varchar2)
is
lv_count number(30);
lv_tot_rec number(30);
lv_succ_rec number(30);
lv_fail_rec number(30);
begin

lv_succ_rec := 0;
lv_fail_rec := 0;

for c in (select * from staging_tab)loop

--validate e_id whether it is present or not
if c.e_id is null then

dbms_output.put_line('E ID is null '||c.e_id);
update staging_tab set validation_status = 'Fail',
validation_msg ='Id is not present' where c.e_id is null;

lv_fail_rec := lv_fail_rec   1;

--validate e_id lengh is not more than 4
elsif length(c.e_id) > 4 then

update staging_tab set validation_status = 'Fail',
validation_msg ='Id length is more than expected' where length(c.e_id) > 4;

lv_fail_rec := lv_fail_rec   1;

else

--Validation process is success and we need to insert into target table
dbms_output.put_line('Inserting into Target table');
insert into target_tab(e_id, e_name, e_loc ) 
select e_id, e_name, e_loc from staging_tab where
e_id = c.e_id and validation_status is null;

lv_succ_rec := lv_succ_rec   1;

end if;

end loop;


select count(1) into lv_count from staging_tab where validation_status = 'Fail';

dbms_output.put_line('Failed rows '||lv_count);

if lv_count >0 then
dbms_output.put_line('Inserting into Reject table');
--Insert failed records into reject table
insert into reject_tab
select * from staging_tab where
validation_status = 'Fail';
--lv_fail_rec := lv_fail_rec   1;
end if;

--end loop;

--Total number of records
Select count(1) into lv_count from staging_tab;
lv_tot_rec := lv_count;

--Inserting into summary table
dbms_output.put_line('Inserting into Summary table');
insert into summary_tab(tot_records,
success_records,
failed_records)
values
(lv_tot_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_stage_target_rej;

Executing SP

set serveroutput on;
declare
err_msg varchar2(4000);
begin
sp_stage_target_rej(err_msg);
dbms_output.put_line(err_msg);
end;

Basically, the Procedure is for inserting success records into the target table and failed records into the reject table. And finally, I am tracking the processed records into the summary table. I hope my procedure will handle this.

In the staging table, there are a total of 6 rows with 3 e_id as null which should go into the reject table and 3 rows that don't contain null e_id should go into a target table

And in summary table it should give total records = 6 success records = 3 failed records = 3

But I am not getting any records in reject table. Can someone help me with this ?

CodePudding user response：

You can do it without loops:

create or replace procedure sp_stage_target_rej(ov_err_msg OUT varchar2)
is
  lv_succ_rec number(30);
  lv_fail_rec number(30);
begin
  lv_succ_rec := 0;
  lv_fail_rec := 0;
  
  UPDATE staging_tab
  SET   validation_status = 'Fail',
        validation_msg    = 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;

  INSERT INTO target_tab(e_id, e_name, e_loc )
  SELECT e_id,
         e_name,
         e_loc
  FROM   staging_tab
  WHERE  validation_status IS NULL;

  lv_succ_rec := SQL%ROWCOUNT;
  
  insert into reject_tab
  select s.*
  from   staging_tab s
  WHERE  validation_status = 'Fail';

  lv_fail_rec := SQL%ROWCOUNT;
  
  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_stage_target_rej;
/

db<>fiddle here