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)
);
Procedure :
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;
Calling a procedure :
set serveroutput on;
declare
v_err_msg varchar2(100);
begin
sp_dup_rec(v_err_msg);
dbms_output.put_line(v_err_msg);
end;
Hi Team, I am getting ora-01422 error while calling a procedure. Basically, I want to insert duplicate records into the cmb_reject tab because the merge statement will fail and I will get ora - 30296 error if I will use only merge. SO, I have written if condition wherein it will fetch the count and if the count is more then will insert into cmb_reject tab
CodePudding user response:
--If there are duplicates id then it should go into cmb_reject table
select e_id into lv_count from cmb_staging;
This raises the exception as you are trying to insert all 5 e_id
values into a single variable and it raises a TOO_MANY_ROWS
exception. (Apart from the fact that it is not identifying duplicates.)
Rather than trying to identify duplicates as a separate part of the process, you can do all the processing in the original UPDATE
(in this case, converting it to a MERGE
statement):
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
MERGE INTO cmb_staging dst
USING (
SELECT ROWID AS rid,
CASE
WHEN e_id IS NULL
THEN 'Id is not present'
WHEN LENGTH(e_id) > 4
THEN 'Id is longer than expected'
WHEN num_e_id > 1
THEN 'Duplicate IDs'
END AS failure_reason
FROM (
SELECT e_id,
COUNT(*) OVER (PARTITION BY e_id) AS num_e_id
FROM cmb_staging
)
WHERE e_id IS NULL
OR LENGTH(e_id) > 4
OR num_e_id > 1
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
UPDATE
SET validation_status = 'Fail',
validation_result = failure_reason;
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;
insert into cmb_reject
select s.*
from cmb_staging 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_dup_rec;
/
db<>fiddle here
CodePudding user response:
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;
CodePudding user response:
This is a problem:
--If there are duplicates id then it should go into cmb_reject table
select e_id into lv_count from cmb_staging;
As there's no WHERE
clause which would restrict number of rows being returned, if cmb_staging
contains 2 (or more) rows, query will fail because you can't put that many rows into a scalar lv_count
variable.
Looks like you actually meant to say
select count(*) into lv_count from cmb_staging;
as the next line says
if lv_count < 1 then