To find repeated rows in a table using pl/sql

Question

I have tried to create either function, block or procedure, to found the repeated rows within a table (not between two tables). I used the default table in Oracle "emp" and of course I duplicated a row, to see the result. This is my code and I haven´t succeded

 declare
  cursor c1 is select * from emp;
  cursor c2 is select * from emp;

  V_REGISTERS c1%rowtype;
  V_REGISTERS2 c2%rowtype;

  BEGIN
   <<OUTER>>
    FOR V_REGISTERS IN c1
      LOOP
        FOR V_REGISTERS2 IN c2

          LOOP
          IF (
              V_REGISTERS.EMPNO=V_REGISTERS2.EMPNO AND
              V_REGISTERS.ENAME=V_REGISTERS2.ENAME AND
              V_REGISTERS.JOB=V_REGISTERS2.JOB AND
              V_REGISTERS.MGR = V_REGISTERS2.MGR AND
              V_REGISTERS.HIREDATE=V_REGISTERS2.HIREDATE AND
              V_REGISTERS.SAL=V_REGISTERS2.SAL AND
              V_REGISTERS.COMM=V_REGISTERS2.COMM AND
              V_REGISTERS.DEPTNO=V_REGISTERS2.DEPTNO

              ) 


             THEN


                dbms_output.put_line('I HAVE FOUND THE REPEATED ROWS');
                dbms_output.put_line(to_char(V_REGISTERS.empno) ||' '|| V_REGISTERS.ename ||' '|| V_REGISTERS.job ||' '|| to_char(V_REGISTERS.mgr) ||' '|| to_char(V_REGISTERS.hiredate)||' '||
                to_char(V_REGISTERS.sal)||' '|| to_char(V_REGISTERS.comm)||' '|| to_char(V_REGISTERS.deptno));

                dbms_output.put_line(to_char(V_REGISTERS2.empno) ||' '|| V_REGISTERS2.ename ||' '|| V_REGISTERS2.job ||' '|| to_char(V_REGISTERS2.mgr) ||' '|| to_char(V_REGISTERS2.hiredate)||' '||
                to_char(V_REGISTERS2.sal)||' '|| to_char(V_REGISTERS2.comm)||' '|| to_char(V_REGISTERS2.deptno));
                dbms_output.put_line('  ');
                dbms_output.put_line('  ');

          END IF;

          END LOOP;
      END LOOP OUTER;
  END;

I´m totally aware of using dynamic functions, and discard pl/sql, like this...

 select * 
  from (
    select f.*, 
    count(*) over (partition by empno,ename,job) ct
    from   emp f
  )
  where  ct > 1

...and get...

7698    BLAKE   MANAGER 7839    01/05/81    2850        30  2
7698    BLAKE   MANAGER 7839    01/05/81    2850        30  2

..but that is not my goal.

Any help or recommendation, would be really appreciate. Thanks

Answer 1

Just use Analytical Functions. RANK() seems to be best fit here.

SELECT list of columns, separated by comma FROM ( SELECT RANK () OVER ( PARTITION BY list of columns, separated by comma ORDER BY RowIdentifier) RNK , * FROM EMP ) RNK WHERE RNK.RNK > 1

This query will partitions rows in the table and gives them a rank. For example if there 3rd record and 5th record are having the same data in those columns you mentioned in the partition clause of the RANK() function then it'll rank 3rd row to 1 and 5th row to 2 and hence in the outer query if you've filtered for ranks more than 1 you'll get all duplicated records and if you filter for ranks = 1, you'll get unique records with no duplicates.

Thank you ☺

Answer 2

The loop you are trying to do also checks with same record so even if it has one record it would display that as a duplicate.. I have modified your cursors to get rownum and the condition to not match the same rownum in the loop would probably give you the correct output.

I have done the below. After creating the table and inserting the duplicate records i ran the script and it returned the duplicate records.

create table emp
(empno number,
ename varchar2(10),
job varchar2(10),
mgr varchar2(10),
hiredate date,
sal number,
comm number,
deptno number);

insert into emp
values
(
7698,    'BLAKE', 'ASSISTANT',  'JERRY',to_date('14/02/2018','dd/mm/yyyy'), 7839,     2850,        30);
insert into emp
values
(
7698,    'BLAKE', 'ASSISTANT',  'JERRY',to_date('14/02/2018','dd/mm/yyyy'), 7839,     2850,        30);
insert into emp
values
(
7698,    'DAN', 'ANALYST',  'TOM',to_date('14/02/2018','dd/mm/yyyy'), 7839,     2850,        30);


declare
  cursor c1 is select EMP.*,ROWNUM from emp ORDER BY EMPNO DESC;
  cursor c2 is select EMP.*,ROWNUM from emp  ORDER BY EMPNO DESC;

  V_REGISTERS c1%rowtype;
  V_REGISTERS2 c2%rowtype;

  BEGIN
   <<OUTER>>
    FOR V_REGISTERS IN c1
      LOOP
        FOR V_REGISTERS2 IN c2

          LOOP
          IF (
              V_REGISTERS.EMPNO=V_REGISTERS2.EMPNO AND
              V_REGISTERS.ENAME=V_REGISTERS2.ENAME AND
              V_REGISTERS.JOB=V_REGISTERS2.JOB AND
              V_REGISTERS.MGR = V_REGISTERS2.MGR AND
              V_REGISTERS.HIREDATE=V_REGISTERS2.HIREDATE AND
              V_REGISTERS.SAL=V_REGISTERS2.SAL AND
              V_REGISTERS.COMM=V_REGISTERS2.COMM AND
              V_REGISTERS.DEPTNO=V_REGISTERS2.DEPTNO AND
              V_REGISTERS.ROWNUM <> V_REGISTERS2.ROWNUM

              ) 


             THEN


                dbms_output.put_line('I HAVE FOUND THE REPEATED ROWS');
                dbms_output.put_line(to_char(V_REGISTERS.empno) ||' '|| V_REGISTERS.ename ||' '|| V_REGISTERS.job ||' '|| to_char(V_REGISTERS.mgr) ||' '|| to_char(V_REGISTERS.hiredate)||' '||
                to_char(V_REGISTERS.sal)||' '|| to_char(V_REGISTERS.comm)||' '|| to_char(V_REGISTERS.deptno));

                dbms_output.put_line(to_char(V_REGISTERS2.empno) ||' '|| V_REGISTERS2.ename ||' '|| V_REGISTERS2.job ||' '|| to_char(V_REGISTERS2.mgr) ||' '|| to_char(V_REGISTERS2.hiredate)||' '||
                to_char(V_REGISTERS2.sal)||' '|| to_char(V_REGISTERS2.comm)||' '|| to_char(V_REGISTERS2.deptno));
                dbms_output.put_line('  ');
                dbms_output.put_line('  ');

          END IF;

          END LOOP;
      END LOOP OUTER;
  END;

Output :

I HAVE FOUND THE REPEATED ROWS

7698 BLAKE ASSISTANT JERRY 14-FEB-18 7839 2850 30

7698 BLAKE ASSISTANT JERRY 14-FEB-18 7839 2850 30

I HAVE FOUND THE REPEATED ROWS

7698 BLAKE ASSISTANT JERRY 14-FEB-18 7839 2850 30

7698 BLAKE ASSISTANT JERRY 14-FEB-18 7839 2850 30

Answer 3

Here's a way of doing it with only one cursor and one pass of the table. We simple remember what the previous row is and compare it to the current one. The "l_in_a_dup_list" is there to only print out 1 occurrence of a duplicate even if there are many, but that is easily removed if you want all occurrences.

SQL> create table t as select * from scott.emp;

Table created.

SQL> insert into t select * from scott.emp where rownum <= 2;

2 rows created.

SQL> insert into t select * from scott.emp where rownum <= 2;

2 rows created.

SQL> insert into t select * from scott.emp where rownum <= 2;

2 rows created.

SQL>
SQL> set serverout on
SQL> declare
  2    --
  3    -- we'll assume that empno/ename/hiredate is the definition of duplicate here
  4    -- but it could be anything (including all columns)
  5    --
  6    l_this_key varchar2(4000);
  7    l_prev_key varchar2(4000) := '<nothing yet>';
  8    l_in_a_dup_list  boolean := false;
  9  begin
 10    for i in (
 11      select * from t
 12      order by empno, ename, hiredate
 13      )
 14    loop
 15      l_this_key := i. empno||'-'||i.ename||'-'||to_char(i.hiredate,'yyyymmddhh24miss');
 16      if l_this_key = l_prev_key
 17      then
 18         if not l_in_a_dup_list then
 19           dbms_output.put_line(l_this_key||' is a duplicate');
 20           l_in_a_dup_list := true;
 21         end if;
 22      else
 23         l_in_a_dup_list := false;
 24      end if;
 25      l_prev_key := l_this_key;
 26    end loop;
 27  end;
 28  /
7369-SMITH-19801217000000 is a duplicate
7499-ALLEN-19810220000000 is a duplicate

PL/SQL procedure successfully completed.

Answer 4

You can use the GROUP BY function, something like this:

DECLARE

BEGIN

FOR empdup IN (SELECT empno,ename,job
                 FROM emp
              GROUP BY empno,ename,job
                HAVING COUNT(1) > 1)
LOOP


   dbms_output.put_line('Dup Record ' || empdup.ename);


END LOOP;

END;