Hi Experts,
Just now I've seen a thread related to finding duplicate records in a collection. I understand that it is not advisable to sort/filter data in a collection.
(https://forums.oracle.com/thread/2584168)
Just for curiosity I tried to display duplicate records in a collection. Please Please .. this is just for practice purpose only. Below is the rough code which I wrote.
I'm aware of one way - can be handled effectively by passing data into a global temporary table and display the duplicate/unique records.
Can you please let me know if there is any other efficient wayto do this.
declare
type emp_rec is record ( ename varchar2(40), empno number);
l_emp_rec emp_rec;
type emp_tab is table of l_emp_rec%type index by binary_integer;
l_emp_tab emp_tab;
l_dup_tab emp_tab;
l_cnt number;
n number :=1;
begin
-- Assigning values to Associative array
l_emp_tab(1).ename := 'suri';
l_emp_tab(1).empno := 1;
l_emp_tab(2).ename := 'surya';
l_emp_tab(2).empno := 2;
l_emp_tab(3).ename := 'suri';
l_emp_tab(3).empno := 1;
-- Comparing collection for duplicate records
for i in l_emp_tab.first..l_emp_tab.last
loop
l_cnt :=0;
for j in l_emp_tab.first..l_emp_tab.last
loop
if l_emp_tab(i).empno = l_emp_tab(j).empno and l_emp_tab(i).ename = l_emp_tab(j).ename then
l_cnt := l_cnt+1;
if l_cnt >=2 then
l_dup_tab(n):= l_emp_tab(i);
end if;
end if;
end loop;
end loop;
-- Displaying duplicate records
for i in l_dup_tab.first..l_dup_tab.last
loop
dbms_output.put_line(l_dup_tab(i).ename||' '||l_dup_tab(i).empno);
end loop;
end;
Cheers,
Suri