Handling Duplicates in Collections In Oracle Database 19c

duplicate_in_collection

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

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

One thought on “PL/SQL Collection Methods”

  1. When you pass an associative array as a parameter to a remote database using a database link, the two databases can have different globalization settings. When the remote database uses a collection method such as An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically (however, a maximum limit is imposed see Referencing Collection Elements ).

Leave a Reply

Your email address will not be published. Required fields are marked *