In our day to day life, sometimes we come across situations where we have to deal with duplicate values within Oracle Collections (Nested Table/Varray/Associative Array).
For every collection mentioned above, there are specific techniques to remove duplicate values.
The Catch here is – Only Nested Table Supports SET operations, but the remaining two collection type [Varray & Associative Array] does not support SET operations.
HANDLING DUPLICATES IN NESTED TABLE
There are several ways we can delete duplicate values from NESTED TABLE :::
- Removing Duplicates Using SET Operator.
- Removing Duplicates Using MULTISET UNION DISTINCT Operator.
- Removing Duplicates From Beginning Using NEXT Method.
- Removing Duplicates From End Using PRIOR Method.
SQL> DECLARE
2 TYPE nt_type3 IS TABLE OF NUMBER;
3 nt_var3 nt_type3;
4 nt_var4 nt_type3;
5 nt_var5 nt_type3;
6 nt_var6 nt_type3;
7 l_row1 NUMBER;
8 l_row2 NUMBER;
9 nval1 NUMBER;
10 nval2 NUMBER;
11 nindx NUMBER := 1;
12 BEGIN
13 nt_var3 := nt_type3 ();
14
15 nt_var3.EXTEND(10);
16 nt_var3(1) := 1;
17 nt_var3(2) := 2;
18 nt_var3(3) := 3;
19 nt_var3(4) := 1;
20 nt_var3(5) := 5;
21 nt_var3(6) := 2;
22 nt_var3(7) := 7;
23 nt_var3(8) := 3;
24 nt_var3(9) := 9;
25 nt_var3(10) := 2;
26
27 /* NT ::: Method - 1 Of Removing Duplicates Using SET */
28
29 IF nt_var3 IS A SET
30 THEN
31 DBMS_OUTPUT.PUT_LINE('nt_var3 Is Unique.');
32 ELSE
33 DBMS_OUTPUT.PUT_LINE('nt_var3 Has Duplicate Values : '||nt_var3.COUNT);
34 END IF;
35
36 DBMS_OUTPUT.PUT_LINE('Looping nt_var3 Before Removing Duplicates');
37
38 FOR rec IN 1..nt_var3.COUNT
39 LOOP
40 DBMS_OUTPUT.PUT_LINE(nt_var3(rec));
41 END LOOP;
42
43 nt_var4 := SET(nt_var3);
44
45 IF nt_var4 IS A SET
46 THEN
47 DBMS_OUTPUT.PUT_LINE('nt_var4 Is Unique : '||nt_var4.COUNT);
48 ELSE
49 DBMS_OUTPUT.PUT_LINE('nt_var4 Has Duplicate Values.');
50 END IF;
51
52 DBMS_OUTPUT.PUT_LINE('Looping nt_var4 After Removing Duplicates');
53
54 FOR rec IN nt_var4.FIRST..nt_var4.COUNT
55 LOOP
56 DBMS_OUTPUT.PUT_LINE(nt_var4(rec));
57 END LOOP;
58
59 /* NT ::: Method - 2 Of Removing Duplicates Using MULTISET UNION DISTINCT */
60
61 nt_var5 := nt_var3;
62
63 IF nt_var3 IS A SET
64 THEN
65 DBMS_OUTPUT.PUT_LINE('nt_var3 Is Unique.');
66 ELSE
67 DBMS_OUTPUT.PUT_LINE('nt_var3 Has Duplicate Values : '||nt_var3.COUNT);
68 END IF;
69
70 DBMS_OUTPUT.PUT_LINE('Looping nt_var3 Before Removing Duplicates');
71
72 FOR rec IN 1..nt_var3.COUNT
73 LOOP
74 DBMS_OUTPUT.PUT_LINE(nt_var3(rec));
75 END LOOP;
76
77 IF nt_var5 IS A SET
78 THEN
79 DBMS_OUTPUT.PUT_LINE('nt_var5 Is Unique.');
80 ELSE
81 DBMS_OUTPUT.PUT_LINE('nt_var5 Has Duplicate Values : '||nt_var5.COUNT);
82 END IF;
83
84 DBMS_OUTPUT.PUT_LINE('Looping nt_var5 Before Removing Duplicates');
85
86 FOR rec IN 1..nt_var5.COUNT
87 LOOP
88 DBMS_OUTPUT.PUT_LINE(nt_var5(rec));
89 END LOOP;
90
91 nt_var6 := nt_var3 MULTISET UNION DISTINCT nt_var5;
92
93 IF nt_var6 IS A SET
94 THEN
95 DBMS_OUTPUT.PUT_LINE('nt_var6 Is Unique : '||nt_var6.COUNT);
96 ELSE
97 DBMS_OUTPUT.PUT_LINE('nt_var6 Has Duplicate Values.');
98 END IF;
99
100 DBMS_OUTPUT.PUT_LINE('Looping nt_var6 After Removing Duplicates');
101
102 FOR rec IN nt_var6.FIRST..nt_var6.LAST
103 LOOP
104 DBMS_OUTPUT.PUT_LINE(nt_var6(rec));
105 END LOOP;
106
107 /* NT ::: Method - 3 Of Removing Duplicates From Beginning */
108
109 IF nt_var3 IS A SET
110 THEN
111 DBMS_OUTPUT.PUT_LINE('nt_var3 Is Unique.');
112 ELSE
113 DBMS_OUTPUT.PUT_LINE('nt_var3 Has Duplicate Values : '||nt_var3.COUNT);
114 END IF;
115
116 DBMS_OUTPUT.PUT_LINE('Looping nt_var3 Before Removing Duplicates');
117
118 FOR rec IN 1..nt_var3.COUNT
119 LOOP
120 DBMS_OUTPUT.PUT_LINE(nt_var3(rec));
121 END LOOP;
122
123 l_row1 := nt_var3.FIRST;
124
125 WHILE (l_row1 IS NOT NULL)
126 LOOP
127 nval1 := nt_var3(l_row1);
128 nindx := l_row1;
129 LOOP
130 l_row2 := nt_var3.NEXT(nindx);
131 EXIT WHEN l_row2 IS NULL;
132 nval2 := nt_var3(l_row2);
133 IF nval1 = nval2 THEN
134 nt_var3.DELETE(l_row2);
135 END IF;
136 nindx := nindx + 1;
137 END LOOP;
138 l_row1 := nt_var3.NEXT(l_row1);
139 END LOOP;
140
141 IF nt_var3 IS A SET
142 THEN
143 DBMS_OUTPUT.PUT_LINE('nt_var3 Is Unique : '||nt_var3.COUNT);
144 ELSE
145 DBMS_OUTPUT.PUT_LINE('nt_var3 Has Duplicate Values.');
146 END IF;
147
148 DBMS_OUTPUT.PUT_LINE('Looping nt_var3 After Removing Duplicates');
149
150 FOR rec IN nt_var3.FIRST..nt_var3.LAST
151 LOOP
152 IF nt_var3.EXISTS(rec) THEN
153 DBMS_OUTPUT.PUT_LINE(nt_var3(rec));
154 END IF;
155 END LOOP;
156
157 nt_var3.DELETE;
158
159 nt_var3.EXTEND(10);
160 nt_var3(1) := 1;
161 nt_var3(2) := 2;
162 nt_var3(3) := 3;
163 nt_var3(4) := 1;
164 nt_var3(5) := 5;
165 nt_var3(6) := 2;
166 nt_var3(7) := 7;
167 nt_var3(8) := 3;
168 nt_var3(9) := 9;
169 nt_var3(10) := 2;
170
171 /* NT ::: Method - 4 Of Removing Duplicates From End */
172
173 IF nt_var3 IS A SET
174 THEN
175 DBMS_OUTPUT.PUT_LINE('nt_var3 Is Unique.');
176 ELSE
177 DBMS_OUTPUT.PUT_LINE('nt_var3 Has Duplicate Values : '||nt_var3.COUNT);
178 END IF;
179
180 DBMS_OUTPUT.PUT_LINE('Looping nt_var3 Before Removing Duplicates');
181
182 FOR rec IN 1..nt_var3.COUNT
183 LOOP
184 DBMS_OUTPUT.PUT_LINE(nt_var3(rec));
185 END LOOP;
186
187 l_row1 := nt_var3.LAST;
188
189 WHILE (l_row1 IS NOT NULL)
190 LOOP
191 nval1 := nt_var3(l_row1);
192 nindx := l_row1;
193 LOOP
194 l_row2 := nt_var3.PRIOR(nindx);
195 EXIT WHEN l_row2 IS NULL;
196 nval2 := nt_var3(l_row2);
197 IF nval1 = nval2 THEN
198 nt_var3.DELETE(l_row2);
199 END IF;
200 nindx := nindx - 1;
201 END LOOP;
202 l_row1 := nt_var3.PRIOR(l_row1);
203 END LOOP;
204
205 IF nt_var3 IS A SET
206 THEN
207 DBMS_OUTPUT.PUT_LINE('nt_var3 Is Unique : '||nt_var3.COUNT);
208 ELSE
209 DBMS_OUTPUT.PUT_LINE('nt_var3 Has Duplicate Values.');
210 END IF;
211
212 DBMS_OUTPUT.PUT_LINE('Looping nt_var3 After Removing Duplicates');
213
214 FOR rec IN nt_var3.FIRST..nt_var3.LAST
215 LOOP
216 IF nt_var3.EXISTS(rec) THEN
217 DBMS_OUTPUT.PUT_LINE(nt_var3(rec));
218 END IF;
219 END LOOP;
220 END;
221 /
--OUTPUT :::
nt_var3 Has Duplicate Values : 10
Looping nt_var3 Before Removing Duplicates
1
2
3
1
5
2
7
3
9
2
nt_var4 Is Unique : 6
Looping nt_var4 After Removing Duplicates
1
2
3
5
7
9
nt_var3 Has Duplicate Values : 10
Looping nt_var3 Before Removing Duplicates
1
2
3
1
5
2
7
3
9
2
nt_var5 Has Duplicate Values : 10
Looping nt_var5 Before Removing Duplicates
1
2
3
1
5
2
7
3
9
2
nt_var6 Is Unique : 6
Looping nt_var6 After Removing Duplicates
1
2
3
5
7
9
nt_var3 Has Duplicate Values : 10
Looping nt_var3 Before Removing Duplicates
1
2
3
1
5
2
7
3
9
2
nt_var3 Is Unique : 6
Looping nt_var3 After Removing Duplicates
1
2
3
5
7
9
nt_var3 Has Duplicate Values : 10
Looping nt_var3 Before Removing Duplicates
1
2
3
1
5
2
7
3
9
2
nt_var3 Is Unique : 6
Looping nt_var3 After Removing Duplicates
1
5
7
3
9
2
HANDLING DUPLICATES IN VARRAY
As VARRAY cannot be made sparse, so in order to delete duplicates from VARRAY, we have to use either the structure of NESTED TABLE or ASSOCIATIVE ARRAY.
SQL> DECLARE
2 TYPE vr_type3 IS VARRAY(50) OF NUMBER;
3 vr_var3 vr_type3;
4 TYPE ar_type3 IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
5 ar_var3 ar_type3;
6 l_row1 NUMBER;
7 l_row2 NUMBER;
8 nval1 NUMBER;
9 nval2 NUMBER;
10 nindx NUMBER := 1;
11 BEGIN
12 /* VR ::: Method - 1 Of Removing Duplicates From Beginning */
13
14 vr_var3 := vr_type3();
15
16 vr_var3.EXTEND(10);
17 vr_var3(1) := 1;
18 vr_var3(2) := 2;
19 vr_var3(3) := 3;
20 vr_var3(4) := 1;
21 vr_var3(5) := 5;
22 vr_var3(6) := 2;
23 vr_var3(7) := 7;
24 vr_var3(8) := 3;
25 vr_var3(9) := 9;
26 vr_var3(10) := 2;
27
28 DBMS_OUTPUT.PUT_LINE('Looping vr_var3 And Populating ar_var3 From vr_var3 Before Removing Duplicates');
29
30 FOR rec IN vr_var3.FIRST..vr_var3.COUNT
31 LOOP
32 DBMS_OUTPUT.PUT_LINE(vr_var3(rec));
33 ar_var3(rec) := vr_var3(rec);
34 END LOOP;
35
36 DBMS_OUTPUT.PUT_LINE('Looping ar_var3 Before Removing Duplicates');
37
38 FOR rec IN ar_var3.FIRST..ar_var3.LAST
39 LOOP
40 DBMS_OUTPUT.PUT_LINE(ar_var3(rec));
41 END LOOP;
42
43 l_row1 := ar_var3.FIRST;
44
45 WHILE (l_row1 IS NOT NULL)
46 LOOP
47 nval1 := ar_var3(l_row1);
48 nindx := l_row1;
49 LOOP
50 l_row2 := ar_var3.NEXT(nindx);
51 EXIT WHEN l_row2 IS NULL;
52 nval2 := ar_var3(l_row2);
53 IF nval1 = nval2 THEN
54 ar_var3.DELETE(l_row2);
55 END IF;
56 nindx := nindx + 1;
57 END LOOP;
58 l_row1 := ar_var3.NEXT(l_row1);
59 END LOOP;
60
61 DBMS_OUTPUT.PUT_LINE('Looping ar_var3 And Populating vr_var3 From ar_var3 After Removing Duplicates');
62
63 nindx := 1;
64
65 vr_var3.DELETE;
66
67 FOR rec IN ar_var3.FIRST..ar_var3.LAST
68 LOOP
69 IF ar_var3.EXISTS(rec) THEN
70 vr_var3.EXTEND;
71 vr_var3(nindx) := ar_var3(rec);
72 nindx := nindx + 1;
73 END IF;
74 END LOOP;
75
76 DBMS_OUTPUT.PUT_LINE('Looping vr_var3 After Removing Duplicates');
77
78 FOR rec IN vr_var3.FIRST..vr_var3.COUNT
79 LOOP
80 DBMS_OUTPUT.PUT_LINE(vr_var3(rec));
81 END LOOP;
82
83 ar_var3.DELETE;
84 vr_var3.DELETE;
85
86 /* VR ::: Method - 2 Of Removing Duplicates From End */
87
88 vr_var3.EXTEND(10);
89 vr_var3(1) := 1;
90 vr_var3(2) := 2;
91 vr_var3(3) := 3;
92 vr_var3(4) := 1;
93 vr_var3(5) := 5;
94 vr_var3(6) := 2;
95 vr_var3(7) := 7;
96 vr_var3(8) := 3;
97 vr_var3(9) := 9;
98 vr_var3(10) := 2;
99
100 DBMS_OUTPUT.PUT_LINE('Looping vr_var3 And Populating ar_var3 From vr_var3 Before Removing Duplicates');
101
102 FOR rec IN vr_var3.FIRST..vr_var3.COUNT
103 LOOP
104 DBMS_OUTPUT.PUT_LINE(vr_var3(rec));
105 ar_var3(rec) := vr_var3(rec);
106 END LOOP;
107
108 DBMS_OUTPUT.PUT_LINE('Looping ar_var3 Before Removing Duplicates');
109
110 FOR rec IN ar_var3.FIRST..ar_var3.LAST
111 LOOP
112 DBMS_OUTPUT.PUT_LINE(ar_var3(rec));
113 END LOOP;
114
115 l_row1 := ar_var3.LAST;
116
117 WHILE (l_row1 IS NOT NULL)
118 LOOP
119 nval1 := ar_var3(l_row1);
120 nindx := l_row1;
121 LOOP
122 l_row2 := ar_var3.PRIOR(nindx);
123 EXIT WHEN l_row2 IS NULL;
124 nval2 := ar_var3(l_row2);
125 IF nval1 = nval2 THEN
126 ar_var3.DELETE(l_row2);
127 END IF;
128 nindx := nindx - 1;
129 END LOOP;
130 l_row1 := ar_var3.PRIOR(l_row1);
131 END LOOP;
132
133 DBMS_OUTPUT.PUT_LINE('Looping ar_var3 And Populating vr_var3 From ar_var3 After Removing Duplicates');
134
135 nindx := 1;
136
137 vr_var3.DELETE;
138
139 FOR rec IN ar_var3.FIRST..ar_var3.LAST
140 LOOP
141 IF ar_var3.EXISTS(rec) THEN
142 vr_var3.EXTEND;
143 vr_var3(nindx) := ar_var3(rec);
144 nindx := nindx + 1;
145 END IF;
146 END LOOP;
147
148 DBMS_OUTPUT.PUT_LINE('Looping vr_var3 After Removing Duplicates');
149
150 FOR rec IN vr_var3.FIRST..vr_var3.COUNT
151 LOOP
152 DBMS_OUTPUT.PUT_LINE(vr_var3(rec));
153 END LOOP;
154 END;
155 /
--OUTPUT :::
Looping vr_var3 And Populating ar_var3 From vr_var3 Before Removing Duplicates
1
2
3
1
5
2
7
3
9
2
Looping ar_var3 Before Removing Duplicates
1
2
3
1
5
2
7
3
9
2
Looping ar_var3 And Populating vr_var3 From ar_var3 After Removing Duplicates
Looping vr_var3 After Removing Duplicates
1
2
3
5
7
9
Looping vr_var3 And Populating ar_var3 From vr_var3 Before Removing Duplicates
1
2
3
1
5
2
7
3
9
2
Looping ar_var3 Before Removing Duplicates
1
2
3
1
5
2
7
3
9
2
Looping ar_var3 And Populating vr_var3 From ar_var3 After Removing Duplicates
Looping vr_var3 After Removing Duplicates
1
5
7
3
9
2
HANDLING DUPLICATES IN ASSOCIATIVE ARRAY
In order to delete duplicates from ASSOCIATIVE ARRAY, we can traverse the collection either using NEXT/PRIOR methods and delete duplicate elements accordingly.
SQL> DECLARE
2 TYPE ar_type3 IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
3 ar_var3 ar_type3;
4 l_row1 NUMBER;
5 l_row2 NUMBER;
6 nval1 NUMBER;
7 nval2 NUMBER;
8 nindx NUMBER := 1;
9 BEGIN
10 ar_var3(1) := 1;
11 ar_var3(2) := 2;
12 ar_var3(3) := 3;
13 ar_var3(4) := 1;
14 ar_var3(5) := 5;
15 ar_var3(6) := 2;
16 ar_var3(7) := 7;
17 ar_var3(8) := 3;
18 ar_var3(9) := 9;
19 ar_var3(10) := 2;
20
21 /* Dense AR ::: Method - 1 Of Removing Duplicates From Beginning */
22
23 DBMS_OUTPUT.PUT_LINE('Looping ar_var3 Before Removing Duplicates');
24
25 FOR rec IN 1..ar_var3.COUNT
26 LOOP
27 DBMS_OUTPUT.PUT_LINE(ar_var3(rec));
28 END LOOP;
29
30 l_row1 := ar_var3.FIRST;
31
32 WHILE (l_row1 IS NOT NULL)
33 LOOP
34 nval1 := ar_var3(l_row1);
35 nindx := l_row1;
36 LOOP
37 l_row2 := ar_var3.NEXT(nindx);
38 EXIT WHEN l_row2 IS NULL;
39 nval2 := ar_var3(l_row2);
40 IF nval1 = nval2 THEN
41 ar_var3.DELETE(l_row2);
42 END IF;
43 nindx := nindx + 1;
44 END LOOP;
45 l_row1 := ar_var3.NEXT(l_row1);
46 END LOOP;
47
48 DBMS_OUTPUT.PUT_LINE('Looping ar_var3 After Removing Duplicates');
49
50 FOR rec IN ar_var3.FIRST..ar_var3.LAST
51 LOOP
52 IF ar_var3.EXISTS(rec) THEN
53 DBMS_OUTPUT.PUT_LINE(ar_var3(rec));
54 END IF;
55 END LOOP;
56
57 ar_var3.DELETE;
58
59 ar_var3(1) := 1;
60 ar_var3(2) := 2;
61 ar_var3(3) := 3;
62 ar_var3(4) := 1;
63 ar_var3(5) := 5;
64 ar_var3(6) := 2;
65 ar_var3(7) := 7;
66 ar_var3(8) := 3;
67 ar_var3(9) := 9;
68 ar_var3(10) := 2;
69
70 /* Dense AR ::: Method - 2 Of Removing Duplicates From End */
71
72 DBMS_OUTPUT.PUT_LINE('Looping ar_var3 Before Removing Duplicates');
73
74 FOR rec IN 1..ar_var3.COUNT
75 LOOP
76 DBMS_OUTPUT.PUT_LINE(ar_var3(rec));
77 END LOOP;
78
79 l_row1 := ar_var3.LAST;
80
81 WHILE (l_row1 IS NOT NULL)
82 LOOP
83 nval1 := ar_var3(l_row1);
84 nindx := l_row1;
85 LOOP
86 l_row2 := ar_var3.PRIOR(nindx);
87 EXIT WHEN l_row2 IS NULL;
88 nval2 := ar_var3(l_row2);
89 IF nval1 = nval2 THEN
90 ar_var3.DELETE(l_row2);
91 END IF;
92 nindx := nindx - 1;
93 END LOOP;
94 l_row1 := ar_var3.PRIOR(l_row1);
95 END LOOP;
96
97 DBMS_OUTPUT.PUT_LINE('Looping ar_var3 After Removing Duplicates');
98
99 FOR rec IN ar_var3.FIRST..ar_var3.LAST
100 LOOP
101 IF ar_var3.EXISTS(rec) THEN
102 DBMS_OUTPUT.PUT_LINE(ar_var3(rec));
103 END IF;
104 END LOOP;
105
106 ar_var3.DELETE;
107
108 ar_var3(-1) := 1;
109 ar_var3(-200) := 2;
110 ar_var3(3) := 3;
111 ar_var3(-237) := 1;
112 ar_var3(678) := 5;
113 ar_var3(-300) := 2;
114 ar_var3(7) := 7;
115 ar_var3(10) := 3;
116 ar_var3(-10) := 9;
117 ar_var3(100) := 2;
118
119 /* Sparse AR ::: Method - 3 Of Removing Duplicates From Beginning */
120
121 DBMS_OUTPUT.PUT_LINE('Looping ar_var3 Before Removing Duplicates');
122
123 FOR rec IN ar_var3.FIRST..ar_var3.LAST
124 LOOP
125 IF ar_var3.EXISTS(rec) THEN
126 DBMS_OUTPUT.PUT_LINE(ar_var3(rec));
127 END IF;
128 END LOOP;
129
130 l_row1 := ar_var3.FIRST;
131
132 WHILE (l_row1 IS NOT NULL)
133 LOOP
134 nval1 := ar_var3(l_row1);
135 nindx := l_row1;
136 LOOP
137 l_row2 := ar_var3.NEXT(nindx);
138 EXIT WHEN l_row2 IS NULL;
139 nval2 := ar_var3(l_row2);
140 IF nval1 = nval2 THEN
141 ar_var3.DELETE(l_row2);
142 END IF;
143 nindx := nindx + 1;
144 END LOOP;
145 l_row1 := ar_var3.NEXT(l_row1);
146 END LOOP;
147
148 DBMS_OUTPUT.PUT_LINE('Looping ar_var3 After Removing Duplicates');
149
150 FOR rec IN ar_var3.FIRST..ar_var3.LAST
151 LOOP
152 IF ar_var3.EXISTS(rec) THEN
153 DBMS_OUTPUT.PUT_LINE(ar_var3(rec));
154 END IF;
155 END LOOP;
156
157 ar_var3.DELETE;
158
159 ar_var3(-1) := 1;
160 ar_var3(-200) := 2;
161 ar_var3(3) := 3;
162 ar_var3(-237) := 1;
163 ar_var3(678) := 5;
164 ar_var3(-300) := 2;
165 ar_var3(7) := 7;
166 ar_var3(10) := 3;
167 ar_var3(-10) := 9;
168 ar_var3(100) := 2;
169
170 /* Sparse AR ::: Method - 4 Of Removing Duplicates From End */
171
172 DBMS_OUTPUT.PUT_LINE('Looping ar_var3 Before Removing Duplicates');
173
174 FOR rec IN ar_var3.FIRST..ar_var3.LAST
175 LOOP
176 IF ar_var3.EXISTS(rec) THEN
177 DBMS_OUTPUT.PUT_LINE(ar_var3(rec));
178 END IF;
179 END LOOP;
180
181 l_row1 := ar_var3.LAST;
182
183 WHILE (l_row1 IS NOT NULL)
184 LOOP
185 nval1 := ar_var3(l_row1);
186 nindx := l_row1;
187 LOOP
188 l_row2 := ar_var3.PRIOR(nindx);
189 EXIT WHEN l_row2 IS NULL;
190 nval2 := ar_var3(l_row2);
191 IF nval1 = nval2 THEN
192 ar_var3.DELETE(l_row2);
193 END IF;
194 nindx := nindx - 1;
195 END LOOP;
196 l_row1 := ar_var3.PRIOR(l_row1);
197 END LOOP;
198
199 DBMS_OUTPUT.PUT_LINE('Looping ar_var3 After Removing Duplicates');
200
201 FOR rec IN ar_var3.FIRST..ar_var3.LAST
202 LOOP
203 IF ar_var3.EXISTS(rec) THEN
204 DBMS_OUTPUT.PUT_LINE(ar_var3(rec));
205 END IF;
206 END LOOP;
207 END;
208 /
--OUTPUT :::
Looping ar_var3 Before Removing Duplicates
1
2
3
1
5
2
7
3
9
2
Looping ar_var3 After Removing Duplicates
1
2
3
5
7
9
Looping ar_var3 Before Removing Duplicates
1
2
3
1
5
2
7
3
9
2
Looping ar_var3 After Removing Duplicates
1
5
7
3
9
2
Looping ar_var3 Before Removing Duplicates
2
1
2
9
1
3
7
3
2
5
Looping ar_var3 After Removing Duplicates
2
1
9
3
7
5
Looping ar_var3 Before Removing Duplicates
2
1
2
9
1
3
7
3
2
5
Looping ar_var3 After Removing Duplicates
9
1
7
3
2
5