All,
We are using Oracle DB 11.2.0.4 on Linux
I am running a complex cursor into a collection and comparing another collection with it which is of the same type obviously. After the above scripts are executed ideally this is how the table would be
Empno Ename Sal Comm Deptno
1 A 100 20 10
2 2A 110 20 20
3 3A 100 20 30
4 4A 110 20 20
5 5A 100 20 30
6 6A 100 20 10
7 7A 100 20 10
8 8a 200 20 10
9 9a 300 20 10
If the above records in a collection and i would want to delete only those index in records in collection which is duplicated in Sal,Comm,Deptno. Just like removing the duplicate rows from a table using the rowid and grouping by Sal,Comm,Deptno. How to achieve this using a collection. Please discuss and help with suggestions.
In the above example it would be Empno
Empno A
Empno 6A
Empno 7A
All these 3 records are having same Sal, Comm,Deptno, further there are few other records too of same nature. Attached the SQL for the script
/* Code for Table creation and Records Inserting */
prompt PL/SQL Developer import file
prompt Created on 02 April 2015
set feedback off
set define off
prompt Creating EMP_DUP...
create table EMP_DUP
(
empno NUMBER(9),
ename VARCHAR2(20),
sal NUMBER(9),
comm NUMBER(9),
deptno NUMBER(9)
);
prompt Disabling triggers for EMP_DUP...
alter table EMP_DUP disable all triggers;
prompt Loading EMP_DUP...
insert into EMP_DUP (empno, ename, sal, comm, deptno)
values (1, 'A', 100, 20, 10);
insert into EMP_DUP (empno, ename, sal, comm, deptno)
values (2, '2A', 110, 20, 20);
insert into EMP_DUP (empno, ename, sal, comm, deptno)
values (3, '3A', 100, 20, 30);
insert into EMP_DUP (empno, ename, sal, comm, deptno)
values (4, '4A', 110, 20, 20);
insert into EMP_DUP (empno, ename, sal, comm, deptno)
values (5, '5A', 100, 20, 30);
insert into EMP_DUP (empno, ename, sal, comm, deptno)
values (6, '6A', 100, 20, 10);
insert into EMP_DUP (empno, ename, sal, comm, deptno)
values (7, '7A', 100, 20, 10);
insert into EMP_DUP (empno, ename, sal, comm, deptno)
values (8, '8A', 200, 20, 10);
insert into EMP_DUP (empno, ename, sal, comm, deptno)
values (9, '9A', 300, 20, 10);
commit;
prompt 9 records loaded
prompt Enabling triggers for EMP_DUP...
alter table EMP_DUP enable all triggers;
set feedback on
set define on
prompt Done.
declare
-- Local variables here
i integer;
Cursor C1 is
Select Empno,Ename, Sal, Comm,Deptno
From Emp_dup
Order by Empno;
type C1_REC_TYPE is table of C1%ROWTYPE index by Pls_integer;
c1_rec_tab c1_rec_type;
begin
Open C1;
Fetch C1 Bulk Collect into C1_Rec_Tab;
Close C1;
dbms_output.put_line(c1_rec_tab.count);
for indx in 1..c1_rec_tab.count
loop
dbms_output.put_line(c1_rec_tab(indx).empno || ' ** '||c1_rec_tab(indx).Sal
|| ' ** '||c1_rec_tab(indx).comm|| ' ** '||c1_rec_tab(indx).deptno);
end loop;
/* */
end;
-- sample of data
Empno Sal Comm Deptno
1 ** 100 ** 20 ** 10 -- Duplicated with Row 1,6,7
2 ** 110 ** 20 ** 20 --Duplicated with Row 2,4
3 ** 100 ** 20 ** 30 --Duplicated with Row 3,5
4 ** 110 ** 20 ** 20
5 ** 100 ** 20 ** 30
6 ** 100 ** 20 ** 10
7 ** 100 ** 20 ** 10
8 ** 200 ** 20 ** 10
9 ** 300 ** 20 ** 10
Thanks in advance