By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table DATA_IMPORT (LEARNER_ID number, USER_ID varchar2(30), ERROR_FLAG varchar2(1), IMPORT_NOTIFICATION varchar2(200));
DECLARE
CURSOR C_DUPLICATE_IMPORT_IDS
IS
SELECT COUNT (D.LEARNER_ID), D.LEARNER_ID
FROM DATA_IMPORT D
-- WHERE D.USER_ID IN (SELECT S.OSUSER
-- FROM V$SESSION S
-- WHERE S.SID IN (SELECT DISTINCT V.SID
-- FROM V$MYSTAT V))
-- sys views not visible here
WHERE 1=1
AND D.ERROR_FLAG = 'N'
-- shoould be NOT NULL; but redundant
-- AND D.LEARNER_ID <> NULL
HAVING COUNT (D.LEARNER_ID) > 1
GROUP BY D.LEARNER_ID;
V_FOUND BOOLEAN := FALSE;
V_DUPLICATE_IMPORT_IDS C_DUPLICATE_IMPORT_IDS%ROWTYPE;
BEGIN
OPEN C_DUPLICATE_IMPORT_IDS;
LOOP
FETCH C_DUPLICATE_IMPORT_IDS INTO V_DUPLICATE_IMPORT_IDS;
EXIT WHEN C_DUPLICATE_IMPORT_IDS%NOTFOUND;
UPDATE DATA_IMPORT D
SET D.ERROR_FLAG = 'Y'
WHERE D.LEARNER_ID = V_DUPLICATE_IMPORT_IDS.LEARNER_ID;
UPDATE DATA_IMPORT D
SET D.IMPORT_NOTIFICATION =
'DUPLICATE LEARNER_ID IDENTIFIED ('
|| V_DUPLICATE_IMPORT_IDS.LEARNER_ID
|| '). LEARNER_IDS ERROR_FLAG SET, THIS CASE WILL NOT IMPORT UNTIL CORRECTED.'
1 rows affected
dbms_output:
THERE ARE NO DUPLICATE LEARNER_IDS WITHIN THIS UPLOAD.
STEP 1 COMPLETED
insert into DATA_IMPORT (LEARNER_ID, ERROR_FLAG)
select 1, 'N' from dual
union all
select 2, 'N' from dual
union all
select 2, 'N' from dual
union all
select 3, 'Y' from dual
union all
select null, 'N' from dual
union all
select null, 'N' from dual;
6 rows affected
DECLARE
CURSOR C_DUPLICATE_IMPORT_IDS
IS
SELECT COUNT (D.LEARNER_ID), D.LEARNER_ID
FROM DATA_IMPORT D
-- WHERE D.USER_ID IN (SELECT S.OSUSER
-- FROM V$SESSION S
-- WHERE S.SID IN (SELECT DISTINCT V.SID
-- FROM V$MYSTAT V))
-- sys views not visible here
WHERE 1=1
AND D.ERROR_FLAG = 'N'
-- shoould be NOT NULL; but redundant
-- AND D.LEARNER_ID <> NULL
HAVING COUNT (D.LEARNER_ID) > 1
GROUP BY D.LEARNER_ID;
V_FOUND BOOLEAN := FALSE;
V_DUPLICATE_IMPORT_IDS C_DUPLICATE_IMPORT_IDS%ROWTYPE;
BEGIN
OPEN C_DUPLICATE_IMPORT_IDS;
LOOP
FETCH C_DUPLICATE_IMPORT_IDS INTO V_DUPLICATE_IMPORT_IDS;
EXIT WHEN C_DUPLICATE_IMPORT_IDS%NOTFOUND;
UPDATE DATA_IMPORT D
SET D.ERROR_FLAG = 'Y'
WHERE D.LEARNER_ID = V_DUPLICATE_IMPORT_IDS.LEARNER_ID;
UPDATE DATA_IMPORT D
SET D.IMPORT_NOTIFICATION =
'DUPLICATE LEARNER_ID IDENTIFIED ('
|| V_DUPLICATE_IMPORT_IDS.LEARNER_ID
|| '). LEARNER_IDS ERROR_FLAG SET, THIS CASE WILL NOT IMPORT UNTIL CORRECTED.'
1 rows affected
dbms_output:
THE FOLLOWING LEARNER WAS IDENTIFIED AS A DUPLICATE 2
STEP 1 COMPLETED