WHAT IS COLLECTION ?
A collection is a data structure that acts like a list or a single-dimensional array of homogeneous elements .Collections are, similar to traditional arrays.
ADVANTAGE OF USING COLLECTIONS :::
- Collections are used to keep track of lists of data elements within programs.Yes, we could use relational tables or global temporary tables (which would involve many context switches) or delimited strings, but collections are very efficient structures that can be manipulated with very clean, maintainable code.
- Collections in conjunction with FORALL and BULK COLLECT to dramatically improve the performance of multirow SQL operations.
- Collections are appropriate for caching database information that is static and frequently queried in a single session to speed up performance of those queries.
COLLECTION METHODS :::
COUNT :-
COUNT method is used to compute the number of elements defined in an ASSOCIATIVE ARRAY,NESTED TABLE,or VARRAY.
- COUNT With Initialized Collection (NESTED TABLE/ASSOCIATIVE ARRAY/VARRAY) With No Elements Return 0.
- COUNT With UnInitialized Collection (NESTED TABLE & VARRAY) Return COLLECTION_IS_NULL Exception.
- COUNT With Uninitialized Collection (ASSOCIATIVE ARRAY) Will have No Effect As Automatic Initialization Take Place For ASSOCIATIVE ARRAY.
After EXTEND,value of COUNT changes.
SQL> DECLARE
2 TYPE nt_tab1 IS TABLE OF NUMBER;
3 --Initialized NESTED TABLE Collection With No Initial Values
4 nt_var1 nt_tab1 := nt_tab1();
5
6 TYPE vr_tab1 IS varray(5) OF NUMBER;
7 --Initialized VARRAY Collection With Initial Values
8 vr_var1 vr_tab1 := vr_tab1(1,2,3,4,5);
9
10 --ASSOCIATIVE ARRAY
11 TYPE ar_tab1 IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
12 ar_var1 ar_tab1;
13
14 TYPE nt_tab2 IS TABLE OF NUMBER;
15 --UnInitialized NESTED TABLE Collection
16 nt_var2 nt_tab2;
17
18 TYPE vr_tab2 IS varray(5) OF NUMBER;
19 --UnInitialized VARRAY Collection
20 vr_var2 vr_tab2;
21
22 ncnt2 NUMBER := NULL;
23 ncnt3 NUMBER := NULL;
24 ncnt4 NUMBER := NULL;
25 ncnt5 NUMBER := NULL;
26 BEGIN
27
28 ncnt2 := vr_var1.COUNT;
29 ncnt3 := ar_var1.COUNT;
30
31 DBMS_OUTPUT.PUT_LINE('COUNT With Initialized Collection (NESTED TABLE) With No Elements Before EXTEND : '||nt_var1.COUNT);
32
33 nt_var1.EXTEND(7);
34
35 DBMS_OUTPUT.PUT_LINE('COUNT With Initialized Collection (NESTED TABLE) With No Elements After EXTEND : '||nt_var1.COUNT);
36
37 DBMS_OUTPUT.PUT_LINE('COUNT With Initialized Collection (VARRAY) With Initial 5 Elements : '||ncnt2);
38
39 DBMS_OUTPUT.PUT_LINE('COUNT With Initialized Collection (ASSOCIATIVE ARRAY) With No Elements : '||ncnt3);
40
41 BEGIN
42 ncnt4 := nt_var2.COUNT;
43 EXCEPTION
44 WHEN COLLECTION_IS_NULL THEN
45 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With COUNT Being Used With Uninitialized NESTED TABLE!!!');
46 END;
47
48 BEGIN
49 ncnt5 := vr_var2.COUNT;
50 EXCEPTION
51 WHEN COLLECTION_IS_NULL THEN
52 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With COUNT Being Used With Uninitialized VARRAY!!!');
53 END;
54 END;
55 /
--OUTPUT :::
COUNT With Initialized Collection (NESTED TABLE) With No Elements Before EXTEND : 0
COUNT With Initialized Collection (NESTED TABLE) With No Elements After EXTEND : 7
COUNT With Initialized Collection (VARRAY) With Initial 5 Elements : 5
COUNT With Initialized Collection (ASSOCIATIVE ARRAY) With No Elements : 0
COLLECTION_IS_NULL Predefined Exception Raised With COUNT Being Used With Uninitialized NESTED TABLE!!!
COLLECTION_IS_NULL Predefined Exception Raised With COUNT Being Used With Uninitialized VARRAY!!!DELETE :-
DELETE method is used to remove elements from an ASSOCIATIVE ARRAY, NESTED TABLE, or VARRAY.
DELETE : Removes all of the elements from a collection.
DELETE(i) : Removes the ith element from the NESTED TABLE or ASSOCIATIVE ARRAY.
DELETE(i,j) : Removes all elements in an inclusive range beginning with i and ending with j. If the collection is a string-indexed associative array, then i and j are strings;otherwise, i and j are integers.
When DELETE is applied to VARRAYs, you should issue DELETE only without arguments (i.e., remove all rows). In other words, you cannot delete individual rows of a VARRAY, possibly making it sparse. The only way to remove a row from a VARRAY is to TRIM from the END of the collection.
If i and/or j refer to nonexistent elements, DELETE attempts to “do the right thing” and will not raise an exception. For example, if you have defined elements in a nested table in index values 1, 2, and 3, then DELETE(–5,1), will remove only the item in position 1. DELETE(–5), on the other hand, will not change the collection.
- DELETE With Initialized Collection With 0 Or More Elements Will Work Fine.No Exception Will Be Raised If We Try To Delete Non-Existent Index.
- DELETE With UnInitialized Collection (NESTED TABLE & VARRAY) Return COLLECTION_IS_NULL Exception.
- DELETE With Uninitialized Collection (ASSOCIATIVE ARRAY) Will have No Effect As Automatic Initialization Take Place For ASSOCIATIVE ARRAY.
SQL> DECLARE
2 TYPE nt_tab1 IS TABLE OF NUMBER;
3 nt_var1 nt_tab1 := nt_tab1(1,2,3,4,5);
4
5 TYPE vr_tab1 IS varray(5) OF NUMBER;
6 vr_var1 vr_tab1 := vr_tab1(6,7,8,9,10);
7
8 TYPE ar_tab1 IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
9 ar_var1 ar_tab1;
10
11 TYPE nt_tab2 IS TABLE OF NUMBER;
12 nt_var2 nt_tab2;
13
14 TYPE vr_tab2 IS varray(5) OF NUMBER;
15 vr_var2 vr_tab2;
16 BEGIN
17
18 BEGIN
19 nt_var2.DELETE;
20 EXCEPTION
21 WHEN COLLECTION_IS_NULL THEN
22 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With DELETE Being Used With Uninitialized NESTED TABLE!!!');
23 END;
24
25 BEGIN
26 vr_var2.DELETE;
27 EXCEPTION
28 WHEN COLLECTION_IS_NULL THEN
29 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With DELETE Being Used With Uninitialized VARRAY!!!');
30 END;
31
32 ar_var1(1) := 500;
33 ar_var1(2) := 1000;
34 ar_var1(3) := 1500;
35 ar_var1(4) := 2000;
36 ar_var1(5) := 2500;
37
38 DBMS_OUTPUT.PUT_LINE('Looping The Content In The NESTED TABLE Before DELETE!!!');
39
40 FOR i in 1..nt_var1.COUNT
41 LOOP
42 DBMS_OUTPUT.PUT_LINE('Index : '||i||' Value : '||nt_var1(i));
43 END LOOP;
44
45 DBMS_OUTPUT.PUT_LINE('Looping The Content In The VARRAY Before DELETE!!!');
46
47 FOR j in 1..vr_var1.COUNT
48 LOOP
49 DBMS_OUTPUT.PUT_LINE('Index : '||j||' Value : '||vr_var1(j));
50 END LOOP;
51
52 DBMS_OUTPUT.PUT_LINE('Looping The Content In The ASSOCIATIVE ARRAY Before DELETE!!!');
53
54 FOR k in 1..ar_var1.COUNT
55 LOOP
56 DBMS_OUTPUT.PUT_LINE('Index : '||k||' Value : '||ar_var1(k));
57 END LOOP;
58
59 nt_var1.DELETE(3);
60 nt_var1.DELETE(1,4);
61 nt_var1.DELETE;
62
63 DBMS_OUTPUT.PUT_LINE('Count Of NESTED TABLE After DELETE!!! '||nt_var1.COUNT);
64
65 vr_var1.DELETE;
66
67 DBMS_OUTPUT.PUT_LINE('Count Of VARRAY After DELETE!!! '||vr_var1.COUNT);
68
69 ar_var1.DELETE(-100);
70 ar_var1.DELETE(-10,5);
71
72 DBMS_OUTPUT.PUT_LINE('Count Of ASSOCIATIVE ARRAY After DELETE!!! '||ar_var1.COUNT);
73 END;
74 /
--OUTPUT :::
COLLECTION_IS_NULL Predefined Exception Raised With DELETE Being Used With Uninitialized NESTED TABLE!!!
COLLECTION_IS_NULL Predefined Exception Raised With DELETE Being Used With Uninitialized VARRAY!!!
Looping The Content In The NESTED TABLE Before DELETE!!!
Index : 1 Value : 1
Index : 2 Value : 2
Index : 3 Value : 3
Index : 4 Value : 4
Index : 5 Value : 5
Looping The Content In The VARRAY Before DELETE!!!
Index : 1 Value : 6
Index : 2 Value : 7
Index : 3 Value : 8
Index : 4 Value : 9
Index : 5 Value : 10
Looping The Content In The ASSOCIATIVE ARRAY Before DELETE!!!
Index : 1 Value : 500
Index : 2 Value : 1000
Index : 3 Value : 1500
Index : 4 Value : 2000
Index : 5 Value : 2500
Count Of NESTED TABLE After DELETE!!! 0
Count Of VARRAY After DELETE!!! 0
Count Of ASSOCIATIVE ARRAY After DELETE!!! 0
EXISTS :-
EXISTS method is used to determine if the specified row/index exists in NESTED TABLE, ASSOCIATIVE ARRAY, and VARRAY.
It ONLY returns either TRUE/FALSE and never returns NULL.
You can use EXISTS beyond the COUNT without raising an exception.
- EXISTS With Initialized Collection (NESTED TABLE/ASSOCIATIVE ARRAY/VARRAY) With No Elements Return FALSE.
- EXISTS With UnInitialized Collection (NESTED TABLE & VARRAY) Return FALSE.
- EXISTS With Uninitialized Collection (ASSOCIATIVE ARRAY) Return FALSE As Automatic Initialization Take Place For ASSOCIATIVE ARRAY.
SQL> DECLARE
2 TYPE nt_tab1 IS TABLE OF NUMBER;
3 nt_var1 nt_tab1 := nt_tab1();
4
5 TYPE vr_tab1 IS varray(5) OF NUMBER;
6 vr_var1 vr_tab1 := vr_tab1();
7
8 TYPE ar_tab1 IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
9 ar_var1 ar_tab1;
10
11 TYPE nt_tab2 IS TABLE OF NUMBER;
12 nt_var2 nt_tab2;
13
14 TYPE vr_tab2 IS varray(5) OF NUMBER;
15 vr_var2 vr_tab2;
16 BEGIN
17 IF nt_var1.EXISTS(2) THEN
18 DBMS_OUTPUT.PUT_LINE('TRUE');
19 ELSE
20 DBMS_OUTPUT.PUT_LINE('EXISTS With Initialized NESTED TABLE With No Elements Returns FALSE!!!');
21 END IF;
22
23 IF vr_var1.EXISTS(2) THEN
24 DBMS_OUTPUT.PUT_LINE('TRUE');
25 ELSE
26 DBMS_OUTPUT.PUT_LINE('EXISTS With Initialized VARRAY With No Elements Returns FALSE!!!');
27 END IF;
28
29 IF ar_var1.EXISTS(2) THEN
30 DBMS_OUTPUT.PUT_LINE('TRUE');
31 ELSE
32 DBMS_OUTPUT.PUT_LINE('EXISTS With Initialized ASSOCIATIVE ARRAY With No Elements Returns FALSE!!!');
33 END IF;
34
35 IF nt_var2.EXISTS(2) THEN
36 DBMS_OUTPUT.PUT_LINE('TRUE');
37 ELSE
38 DBMS_OUTPUT.PUT_LINE('EXISTS With UnInitialized NESTED TABLE Returns FALSE!!!');
39 END IF;
40
41 IF vr_var2.EXISTS(2) THEN
42 DBMS_OUTPUT.PUT_LINE('TRUE');
43 ELSE
44 DBMS_OUTPUT.PUT_LINE('EXISTS With UnInitialized VARRAY Returns FALSE!!!');
45 END IF;
46
47 nt_var1.EXTEND(5);
48 nt_var1(1) := 1;
49 nt_var1(2) := 2;
50 nt_var1(3) := 3;
51 nt_var1(4) := 4;
52 nt_var1(5) := 5;
53
54 vr_var1.EXTEND(5);
55 vr_var1(1) := 6;
56 vr_var1(2) := 7;
57 vr_var1(3) := 8;
58 vr_var1(4) := 9;
59 vr_var1(5) := 10;
60
61 ar_var1(1) := 11;
62 ar_var1(2) := 12;
63 ar_var1(3) := 13;
64 ar_var1(4) := 14;
65 ar_var1(5) := 15;
66
67 IF nt_var1.EXISTS(1) THEN
68 DBMS_OUTPUT.PUT_LINE('1st Element Exists In NESTED TABLE : '||nt_var1(1));
69 END IF;
70
71 IF nt_var1.EXISTS(100) THEN
72 DBMS_OUTPUT.PUT_LINE('100th Element Exists In NESTED TABLE : '||nt_var1(100));
73 END IF;
74
75 IF vr_var1.EXISTS(1) THEN
76 DBMS_OUTPUT.PUT_LINE('1st Element Exists In VARRAY : '||vr_var1(1));
77 END IF;
78
79 IF vr_var1.EXISTS(100) THEN
80 DBMS_OUTPUT.PUT_LINE('100th Element Exists In VARRAY : '||vr_var1(100));
81 END IF;
82
83 IF ar_var1.EXISTS(1) THEN
84 DBMS_OUTPUT.PUT_LINE('1st Element Exists In ASSOCIATIVE ARRAY : '||ar_var1(1));
85 END IF;
86
87 IF ar_var1.EXISTS(100) THEN
88 DBMS_OUTPUT.PUT_LINE('1ooth Element Exists In ASSOCIATIVE ARRAY : '||ar_var1(100));
89 END IF;
90 END;
91 /
--OUTPUT :::
EXISTS With Initialized NESTED TABLE With No Elements Returns FALSE!!!
EXISTS With Initialized VARRAY With No Elements Returns FALSE!!!
EXISTS With Initialized ASSOCIATIVE ARRAY With No Elements Returns FALSE!!!
EXISTS With UnInitialized NESTED TABLE Returns FALSE!!!
EXISTS With UnInitialized VARRAY Returns FALSE!!!
1st Element Exists In NESTED TABLE : 1
1st Element Exists In VARRAY : 6
1st Element Exists In ASSOCIATIVE ARRAY : 11
EXTEND :-
EXTEND method is used to allocate memory slot in a nested table or a VARRAY.If you have defined a nested table or a VARRAY TYPE and then create collection variable of that type,those collections are still NOT ready to store values.In order to store values, first we have allocate memory for each elements ( using EXTEND method ) and then we can store values in those collections.
Do not use EXTEND with associative arrays.
EXTEND with no arguments Appends a single null element.
EXTEND(n) Appends n null elements.
EXTEND(n,i) Appends n elements and sets each to the same value as the ith element; this form of EXTEND is required for collections with NOT NULL elements.
- An Attempt To EXTEND VARRAY Beyond Its Declared Limit Will Raise ORA-06532: Subscript outside of limit Exception.
- EXTEND With UnInitialized Collection (NESTED TABLE & VARRAY) Return COLLECTION_IS_NULL Exception.
SQL> DECLARE
2 TYPE nt_tab1 IS TABLE OF NUMBER;
3 nt_var1 nt_tab1 := nt_tab1();
4
5 TYPE vr_tab1 IS varray(5) OF NUMBER;
6 vr_var1 vr_tab1 := vr_tab1();
7
8 TYPE ar_tab1 IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
9 ar_var1 ar_tab1;
10
11 TYPE nt_tab2 IS TABLE OF NUMBER;
12 nt_var2 nt_tab2;
13
14 TYPE vr_tab2 IS varray(5) OF NUMBER;
15 vr_var2 vr_tab2;
16 BEGIN
17 nt_var1.EXTEND;
18 nt_var1.EXTEND(2);
19 nt_var1(1) := 1;
20 nt_var1(2) := 2;
21 nt_var1(3) := 3;
22
23 vr_var1.EXTEND(3);
24 vr_var1(1) := 4;
25 vr_var1(2) := 5;
26 vr_var1(3) := 6;
27 vr_var1.EXTEND(2,3);
28
29 DBMS_OUTPUT.PUT_LINE('Looping The Content In The NESTED TABLE After EXTEND!!!');
30
31 FOR i in 1..nt_var1.COUNT
32 LOOP
33 DBMS_OUTPUT.PUT_LINE('Index : '||i||' Value : '||nt_var1(i));
34 END LOOP;
35
36 DBMS_OUTPUT.PUT_LINE('Looping The Content In The VARRAY After EXTEND!!!');
37
38 FOR j in 1..vr_var1.COUNT
39 LOOP
40 DBMS_OUTPUT.PUT_LINE('Index : '||j||' Value : '||vr_var1(j));
41 END LOOP;
42
43 nt_var1.DELETE(3);
44
45 DBMS_OUTPUT.PUT_LINE('Looping The Content In The NESTED TABLE After Deleting 3rd Element!!!');
46
47 FOR i in 1..nt_var1.COUNT
48 LOOP
49 DBMS_OUTPUT.PUT_LINE('Index : '||i||' Value : '||nt_var1(i));
50 END LOOP;
51
52 nt_var1(3) := 3;
53 nt_var1.EXTEND;
54 nt_var1(4) := 4;
55
56 DBMS_OUTPUT.PUT_LINE('Looping The Content In The NESTED TABLE After EXTEND!!!');
57
58 FOR i in 1..nt_var1.COUNT
59 LOOP
60 DBMS_OUTPUT.PUT_LINE('Index : '||i||' Value : '||nt_var1(i));
61 END LOOP;
62
63 BEGIN
64 nt_var2.EXTEND;
65 EXCEPTION
66 WHEN COLLECTION_IS_NULL THEN
67 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With EXTEND Being Used With Uninitialized NESTED TABLE!!!');
68 END;
69
70 BEGIN
71 vr_var2.EXTEND;
72 EXCEPTION
73 WHEN COLLECTION_IS_NULL THEN
74 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With EXTEND Being Used With Uninitialized VARRAY!!!');
75 END;
76
77 BEGIN
78 vr_var1.EXTEND;
79 EXCEPTION
80 WHEN OTHERS THEN
81 DBMS_OUTPUT.PUT_LINE('An Attempt To EXTEND VARRAY Beyond Its Declared Limit Will Raise ::: '||DBMS_UTILITY.FORMAT_ERROR_STACK);
82 END;
83 END;
84 /
--OUTPUT :::
Looping The Content In The NESTED TABLE After EXTEND!!!
Index : 1 Value : 1
Index : 2 Value : 2
Index : 3 Value : 3
Looping The Content In The VARRAY After EXTEND!!!
Index : 1 Value : 4
Index : 2 Value : 5
Index : 3 Value : 6
Index : 4 Value : 6
Index : 5 Value : 6
Looping The Content In The NESTED TABLE After Deleting 3rd Element!!!
Index : 1 Value : 1
Index : 2 Value : 2
Looping The Content In The NESTED TABLE After EXTEND!!!
Index : 1 Value : 1
Index : 2 Value : 2
Index : 3 Value : 3
Index : 4 Value : 4
COLLECTION_IS_NULL Predefined Exception Raised With EXTEND Being Used With Uninitialized NESTED TABLE!!!
COLLECTION_IS_NULL Predefined Exception Raised With EXTEND Being Used With Uninitialized VARRAY!!!
An Attempt To EXTEND VARRAY Beyond Its Declared Limit Will Raise ::: ORA-06532: Subscript outside of limit
FIRST & LAST :-
Use the FIRST and LAST methods with nested tables, associative arrays, and VARRAYs to return, respectively, the highest and lowest index values defined in the collection.
For string-indexed associative arrays, these methods return strings; “lowest” and “highest” are determined by the ordering of the character set in use in that session. For all other collection types, these methods return integers.
For VARRAYs, which have at least one element, FIRST,LAST & COUNT is always 1.
- FIRST & LAST With Initialized Collection (NESTED TABLE/ASSOCIATIVE ARRAY/VARRAY) With No Elements Return NULL.
- FIRST & LAST With UnInitialized Collection (NESTED TABLE & VARRAY) Return COLLECTION_IS_NULL Exception.
- FIRST & LAST With UnInitialized Collection (ASSOCIATIVE ARRAY) Return NULL.
SQL> DECLARE
2 TYPE nt_tab1 IS TABLE OF NUMBER;
3 nt_var1 nt_tab1 := nt_tab1();
4
5 TYPE vr_tab1 IS varray(6) OF NUMBER;
6 vr_var1 vr_tab1 := vr_tab1();
7
8 TYPE ar_tab1 IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
9 ar_var1 ar_tab1;
10
11 TYPE nt_tab2 IS TABLE OF NUMBER;
12 nt_var2 nt_tab2;
13
14 TYPE vr_tab2 IS varray(5) OF NUMBER;
15 vr_var2 vr_tab2;
16
17 nrownum1 NUMBER;
18 nrownum2 NUMBER;
19 nrownum3 NUMBER;
20 nrownum4 NUMBER;
21 BEGIN
22
23 DBMS_OUTPUT.PUT_LINE('Initialized NESTED TABLE With No Elements Returns NULL For FIRST : '||nt_var1.FIRST);
24 DBMS_OUTPUT.PUT_LINE('Initialized NESTED TABLE With No Elements Returns NULL For LAST : '||nt_var1.LAST);
25 DBMS_OUTPUT.PUT_LINE('Initialized VARRAY With No Elements Returns NULL For FIRST : '||vr_var1.FIRST);
26 DBMS_OUTPUT.PUT_LINE('Initialized VARRAY With No Elements Returns NULL For LAST : '||vr_var1.LAST);
27 DBMS_OUTPUT.PUT_LINE('Initialized ASSOCIATIVE ARRAY With No Elements Returns NULL For FIRST : '||ar_var1.FIRST);
28 DBMS_OUTPUT.PUT_LINE('Initialized ASSOCIATIVE ARRAY With No Elements Returns NULL For LAST : '||ar_var1.LAST);
29
30 BEGIN
31 nrownum1 := nt_var2.FIRST;
32 EXCEPTION
33 WHEN COLLECTION_IS_NULL THEN
34 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With FIRST Being Used With Uninitialized NESTED TABLE!!!');
35 END;
36
37 BEGIN
38 nrownum2 := nt_var2.LAST;
39 EXCEPTION
40 WHEN COLLECTION_IS_NULL THEN
41 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With LAST Being Used With Uninitialized NESTED TABLE!!!');
42 END;
43
44 BEGIN
45 nrownum3 := vr_var2.FIRST;
46 EXCEPTION
47 WHEN COLLECTION_IS_NULL THEN
48 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With FIRST Being Used With Uninitialized VARRAY!!!');
49 END;
50
51 BEGIN
52 nrownum4 := vr_var2.LAST;
53 EXCEPTION
54 WHEN COLLECTION_IS_NULL THEN
55 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With LAST Being Used With Uninitialized VARRAY!!!');
56 END;
57
58 nt_var1.EXTEND(5);
59 nt_var1(1) := 1;
60 nt_var1(2) := 2;
61 nt_var1(3) := 3;
62 nt_var1(4) := 4;
63 nt_var1(5) := 5;
64
65 vr_var1.EXTEND(6);
66 vr_var1(1) := 6;
67 vr_var1(2) := 7;
68 vr_var1(3) := 8;
69 vr_var1(4) := 9;
70 vr_var1(5) := 10;
71 vr_var1(6) := 11;
72
73 ar_var1(1) := 12;
74 ar_var1(2) := 13;
75 ar_var1(3) := 14;
76
77 DBMS_OUTPUT.PUT_LINE('Initialized NESTED TABLE With Elements For FIRST : '||nt_var1.FIRST);
78 DBMS_OUTPUT.PUT_LINE('Initialized NESTED TABLE With Elements For LAST : '||nt_var1.LAST);
79 DBMS_OUTPUT.PUT_LINE('Initialized VARRAY With Elements For FIRST : '||vr_var1.FIRST);
80 DBMS_OUTPUT.PUT_LINE('Initialized VARRAY With Elements For LAST : '||vr_var1.LAST);
81 DBMS_OUTPUT.PUT_LINE('Initialized ASSOCIATIVE ARRAY With Elements For FIRST : '||ar_var1.FIRST);
82 DBMS_OUTPUT.PUT_LINE('Initialized ASSOCIATIVE ARRAY With Elements For LAST : '||ar_var1.LAST);
83 END;
84 /
--OUTPUT :::
Initialized NESTED TABLE With No Elements Returns NULL For FIRST :
Initialized NESTED TABLE With No Elements Returns NULL For LAST :
Initialized VARRAY With No Elements Returns NULL For FIRST :
Initialized VARRAY With No Elements Returns NULL For LAST :
Initialized ASSOCIATIVE ARRAY With No Elements Returns NULL For FIRST :
Initialized ASSOCIATIVE ARRAY With No Elements Returns NULL For LAST :
COLLECTION_IS_NULL Predefined Exception Raised With FIRST Being Used With Uninitialized NESTED TABLE!!!
COLLECTION_IS_NULL Predefined Exception Raised With LAST Being Used With Uninitialized NESTED TABLE!!!
COLLECTION_IS_NULL Predefined Exception Raised With FIRST Being Used With Uninitialized VARRAY!!!
COLLECTION_IS_NULL Predefined Exception Raised With LAST Being Used With Uninitialized VARRAY!!!
Initialized NESTED TABLE With Elements For FIRST : 1
Initialized NESTED TABLE With Elements For LAST : 5
Initialized VARRAY With Elements For FIRST : 1
Initialized VARRAY With Elements For LAST : 6
Initialized ASSOCIATIVE ARRAY With Elements For FIRST : 1
Initialized ASSOCIATIVE ARRAY With Elements For LAST : 3
LIMIT :-
LIMIT method is used to determine the maximum number of elements that can be defined in a VARRAY.
Do not use LIMIT with associative arrays.
- LIMIT With Initialized Collection (NESTED TABLE) With No Elements Return NULL.
- LIMIT With Initialized Collection (VARRAY) With No Elements Return Size Of VARRAY.
- LIMIT With UnInitialized Collection (NESTED TABLE & VARRAY) Return COLLECTION_IS_NULL Exception.
SQL> DECLARE
2 TYPE nt_tab1 IS TABLE OF NUMBER;
3 nt_var1 nt_tab1 := nt_tab1();
4
5 TYPE vr_tab1 IS varray(5) OF NUMBER;
6 vr_var1 vr_tab1 := vr_tab1();
7
8 TYPE nt_tab2 IS TABLE OF NUMBER;
9 nt_var2 nt_tab2;
10
11 TYPE vr_tab2 IS varray(5) OF NUMBER;
12 vr_var2 vr_tab2;
13
14 nvalue NUMBER := 2;
15
16 noutput1 NUMBER;
17 noutput2 NUMBER;
18
19 noutput3 NUMBER;
20 noutput4 NUMBER;
21
22 BEGIN
23 DBMS_OUTPUT.PUT_LINE('LIMIT With Initialized NESTED TABLE With No Elements Returns NULL!!!'||nt_var1.LIMIT);
24 DBMS_OUTPUT.PUT_LINE('LIMIT With Initialized VARRAY With No Elements Returns The Limit ::: '||vr_var1.LIMIT);
25
26 vr_var1.EXTEND;
27 vr_var1(1) := 1;
28
29 LOOP
30 IF vr_var1.LAST < vr_var1.LIMIT THEN
31 vr_var1.EXTEND;
32 vr_var1(vr_var1.LAST) := nvalue;
33 nvalue := nvalue + 1;
34 ELSE
35 EXIT;
36 END IF;
37 END LOOP;
38
39 DBMS_OUTPUT.PUT_LINE('Looping The Content In The VARRAY !!!');
40
41 FOR i in 1..vr_var1.COUNT
42 LOOP
43 DBMS_OUTPUT.PUT_LINE('Index : '||i||' Value : '||vr_var1(i));
44 END LOOP;
45
46
47 BEGIN
48 noutput1 := vr_var2.LIMIT;
49 EXCEPTION
50 WHEN COLLECTION_IS_NULL THEN
51 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With LIMIT Being Used With Uninitialized VARRAY!!!');
52 END;
53
54 BEGIN
55 noutput2 := nt_var2.LIMIT;
56 EXCEPTION
57 WHEN COLLECTION_IS_NULL THEN
58 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With LIMIT Being Used With Uninitialized NESTED TABLE!!!');
59 END;
60 END;
61 /
--OUTPUT :::
LIMIT With Initialized NESTED TABLE With No Elements Returns NULL!!!
LIMIT With Initialized VARRAY With No Elements Returns The Limit ::: 5
Looping The Content In The VARRAY !!!
Index : 1 Value : 1
Index : 2 Value : 2
Index : 3 Value : 3
Index : 4 Value : 4
Index : 5 Value : 5
COLLECTION_IS_NULL Predefined Exception Raised With LIMIT Being Used With Uninitialized VARRAY!!!
COLLECTION_IS_NULL Predefined Exception Raised With LIMIT Being Used With Uninitialized NESTED TABLE!!!
NEXT & PRIOR :-
Use the PRIOR and NEXT methods with nested tables, associative arrays, and VARRAYs to navigate through the contents of a collection.PRIOR returns the next-lower index value in use relative to i; NEXT returns the next higher.
If i is greater than or equal to LAST/COUNT, NEXT returns NULL.
If i is less than or equal to FIRST, PRIOR returns NULL.
- NEXT & PRIOR With Initialized Collection (NESTED TABLE/VARRAY/ASSOCIATIVE ARRAY) With No Elements Return NULL.
- NEXT & PRIOR With UnInitialized Collection (NESTED TABLE & VARRAY) Return COLLECTION_IS_NULL Exception.
- NEXT & PRIOR With UnInitialized Collection (ASSOCIATIVE ARRAY) Return NULL.
SQL> DECLARE
2 TYPE nt_tab1 IS TABLE OF NUMBER;
3 nt_var1 nt_tab1 := nt_tab1();
4
5 TYPE vr_tab1 IS varray(6) OF NUMBER;
6 vr_var1 vr_tab1 := vr_tab1();
7
8 TYPE ar_tab1 IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
9 ar_var1 ar_tab1;
10
11 TYPE nt_tab2 IS TABLE OF NUMBER;
12 nt_var2 nt_tab2;
13
14 TYPE vr_tab2 IS varray(5) OF NUMBER;
15 vr_var2 vr_tab2;
16
17 l_row1 NUMBER;
18 l_index VARCHAR2(10);
19
20 BEGIN
21
22 DBMS_OUTPUT.PUT_LINE('PRIOR With Initialized NESTED TABLE With No Elements Returns NULL!!!');
23 DBMS_OUTPUT.PUT_LINE('NEXT With Initialized NESTED TABLE With No Elements Returns NULL!!!');
24 DBMS_OUTPUT.PUT_LINE('PRIOR With Initialized VARRAY With No Elements Returns NULL!!!');
25 DBMS_OUTPUT.PUT_LINE('NEXT With Initialized VARRAY With No Elements Returns NULL!!!');
26 DBMS_OUTPUT.PUT_LINE('PRIOR With Initialized ASSOCIATIVE ARRAY With No Elements Returns NULL!!!');
27 DBMS_OUTPUT.PUT_LINE('NEXT With Initialized ASSOCIATIVE ARRAY With No Elements Returns NULL!!!');
28
29 BEGIN
30 l_row1 := nt_var2.FIRST;
31 WHILE (l_row1 IS NOT NULL)
32 LOOP
33 DBMS_OUTPUT.PUT_LINE(nt_var2 (l_row1));
34 l_row1 := nt_var2.NEXT (l_row1);
35 END LOOP;
36 EXCEPTION
37 WHEN COLLECTION_IS_NULL THEN
38 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With NEXT Being Used With Uninitialized NESTED TABLE!!!');
39 END;
40
41 l_row1 := NULL;
42
43 BEGIN
44 l_row1 := vr_var2.FIRST;
45 WHILE (l_row1 IS NOT NULL)
46 LOOP
47 DBMS_OUTPUT.PUT_LINE(vr_var2 (l_row1));
48 l_row1 := vr_var2.NEXT (l_row1);
49 END LOOP;
50 EXCEPTION
51 WHEN COLLECTION_IS_NULL THEN DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With NEXT Being Used With Uninitialized VARRAY!!!');
52 END;
53
54 l_row1 := NULL;
55
56 BEGIN
57 l_row1 := nt_var2.LAST;
58 WHILE (l_row1 IS NOT NULL)
59 LOOP
60 DBMS_OUTPUT.PUT_LINE(nt_var2 (l_row1));
61 l_row1 := nt_var2.PRIOR (l_row1);
62 END LOOP;
63 EXCEPTION
64 WHEN COLLECTION_IS_NULL THEN
65 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With PRIOR Being Used With Uninitialized NESTED TABLE!!!');
66 END;
67
68 l_row1 := NULL;
69
70 BEGIN
71 l_row1 := vr_var2.LAST;
72 WHILE (l_row1 IS NOT NULL)
73 LOOP
74 DBMS_OUTPUT.PUT_LINE(vr_var2 (l_row1));
75 l_row1 := vr_var2.PRIOR (l_row1);
76 END LOOP;
77 EXCEPTION
78 WHEN COLLECTION_IS_NULL THEN
79 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With PRIOR Being Used With Uninitialized VARRAY!!!');
80 END;
81
82
83 nt_var1.EXTEND(5);
84 nt_var1(1) := 1;
85 nt_var1(2) := 2;
86 nt_var1(3) := 3;
87 nt_var1(4) := 4;
88 nt_var1(5) := 5;
89
90 vr_var1.EXTEND(6);
91 vr_var1(1) := 6;
92 vr_var1(2) := 7;
93 vr_var1(3) := 8;
94 vr_var1(4) := 9;
95 vr_var1(5) := 10;
96 vr_var1(6) := 11;
97
98 ar_var1('c') := 1;
99 ar_var1('H') := 2;
100 ar_var1('z') := 3;
101 ar_var1('A') := 4;
102 ar_var1('l') := 5;
103
104 l_row1 := NULL;
105
106 l_row1 := nt_var1.FIRST;
107
108 DBMS_OUTPUT.PUT_LINE('Looping NESTED TABLE Using NEXT!!!');
109
110 WHILE (l_row1 IS NOT NULL)
111 LOOP
112 DBMS_OUTPUT.PUT_LINE(nt_var1 (l_row1));
113 l_row1 := nt_var1.NEXT (l_row1);
114 END LOOP;
115
116 l_row1 := NULL;
117
118 l_row1 := nt_var1.LAST;
119
120 DBMS_OUTPUT.PUT_LINE('Looping NESTED TABLE Using PRIOR!!!');
121
122 WHILE (l_row1 IS NOT NULL)
123 LOOP
124 DBMS_OUTPUT.PUT_LINE(nt_var1 (l_row1));
125 l_row1 := nt_var1.PRIOR (l_row1);
126 END LOOP;
127
128 l_row1 := NULL;
129
130 l_row1 := vr_var1.FIRST;
131
132 DBMS_OUTPUT.PUT_LINE('Looping VARRAY Using NEXT!!!');
133
134 WHILE (l_row1 IS NOT NULL)
135 LOOP
136 DBMS_OUTPUT.PUT_LINE(vr_var1 (l_row1));
137 l_row1 := vr_var1.NEXT (l_row1);
138 END LOOP;
139
140 l_row1 := NULL;
141
142 l_row1 := vr_var1.LAST;
143
144 DBMS_OUTPUT.PUT_LINE('Looping VARRAY Using PRIOR!!!');
145
146 WHILE (l_row1 IS NOT NULL)
147 LOOP
148 DBMS_OUTPUT.PUT_LINE(vr_var1 (l_row1));
149 l_row1 := vr_var1.PRIOR (l_row1);
150 END LOOP;
151
152 l_index := NULL;
153
154 l_index := ar_var1.FIRST;
155
156 DBMS_OUTPUT.PUT_LINE('Looping ASSOCIATIVE ARRAY Using NEXT!!!');
157
158 WHILE (l_index IS NOT NULL)
159 LOOP
160 DBMS_OUTPUT.PUT_LINE(ar_var1 (l_index));
161 l_index := ar_var1.NEXT (l_index);
162 END LOOP;
163
164 l_index := NULL;
165
166 l_index := ar_var1.LAST;
167
168 DBMS_OUTPUT.PUT_LINE('Looping ASSOCIATIVE ARRAY Using PRIOR!!!');
169
170 WHILE (l_index IS NOT NULL)
171 LOOP
172 DBMS_OUTPUT.PUT_LINE(ar_var1 (l_index));
173 l_index := ar_var1.PRIOR (l_index);
174 END LOOP;
175 END;
176 /
--OUTPUT :::
PRIOR With Initialized NESTED TABLE With No Elements Returns NULL!!!
NEXT With Initialized NESTED TABLE With No Elements Returns NULL!!!
PRIOR With Initialized VARRAY With No Elements Returns NULL!!!
NEXT With Initialized VARRAY With No Elements Returns NULL!!!
PRIOR With Initialized ASSOCIATIVE ARRAY With No Elements Returns NULL!!!
NEXT With Initialized ASSOCIATIVE ARRAY With No Elements Returns NULL!!!
COLLECTION_IS_NULL Predefined Exception Raised With NEXT Being Used With Uninitialized NESTED TABLE!!!
COLLECTION_IS_NULL Predefined Exception Raised With NEXT Being Used With Uninitialized VARRAY!!!
COLLECTION_IS_NULL Predefined Exception Raised With PRIOR Being Used With Uninitialized NESTED TABLE!!!
COLLECTION_IS_NULL Predefined Exception Raised With PRIOR Being Used With Uninitialized VARRAY!!!
Looping NESTED TABLE Using NEXT!!!
1
2
3
4
5
Looping NESTED TABLE Using PRIOR!!!
5
4
3
2
1
Looping VARRAY Using NEXT!!!
6
7
8
9
10
11
Looping VARRAY Using PRIOR!!!
11
10
9
8
7
6
Looping ASSOCIATIVE ARRAY Using NEXT!!!
4
2
1
5
3
Looping ASSOCIATIVE ARRAY Using PRIOR!!!
3
5
1
2
4
TRIM :-
Use TRIM to remove n elements from the END of a nested table or VARRAY. Without arguments, TRIM removes exactly one element.
Attempting to TRIM an associative array will produce a compile-time error.
- TRIM With Initialized Collection (NESTED TABLE & VARRAY) With No Elements Return ORA-06533: Subscript beyond count Exception.
- TRIM With UnInitialized Collection (NESTED TABLE & VARRAY) Return COLLECTION_IS_NULL Exception.
SQL> DECLARE
2 TYPE nt_tab1 IS TABLE OF NUMBER;
3 nt_var1 nt_tab1 := nt_tab1();
4
5 TYPE vr_tab1 IS varray(6) OF NUMBER;
6 vr_var1 vr_tab1 := vr_tab1();
7
8 TYPE ar_tab1 IS TABLE OF NUMBER INDEX BY VARCHAR2(10);
9 ar_var1 ar_tab1;
10
11 TYPE nt_tab2 IS TABLE OF NUMBER;
12 nt_var2 nt_tab2;
13
14 TYPE vr_tab2 IS varray(5) OF NUMBER;
15 vr_var2 vr_tab2;
16
17 l_row1 NUMBER;
18 BEGIN
19 nt_var1.EXTEND(5);
20 nt_var1(1) := 1;
21 nt_var1(2) := 2;
22 nt_var1(3) := 3;
23 nt_var1(4) := 4;
24 nt_var1(5) := 5;
25
26 vr_var1.EXTEND(6);
27 vr_var1(1) := 6;
28 vr_var1(2) := 7;
29 vr_var1(3) := 8;
30 vr_var1(4) := 9;
31 vr_var1(5) := 10;
32 vr_var1(6) := 11;
33
34 BEGIN
35 nt_var2.TRIM;
36 EXCEPTION
37 WHEN COLLECTION_IS_NULL THEN
38 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With TRIM Being Used With Uninitialized NESTED TABLE!!!');
39 END;
40
41 BEGIN
42 vr_var2.TRIM;
43 EXCEPTION
44 WHEN COLLECTION_IS_NULL THEN
45 DBMS_OUTPUT.PUT_LINE('COLLECTION_IS_NULL Predefined Exception Raised With TRIM Being Used With Uninitialized VARRAY!!!');
46 END;
47
48 DBMS_OUTPUT.PUT_LINE('Looping The Content In The NESTED TABLE Before TRIM!!!');
49
50 FOR i in 1..nt_var1.COUNT
51 LOOP
52 DBMS_OUTPUT.PUT_LINE('Index : '||i||' Value : '||nt_var1(i));
53 END LOOP;
54
55 DBMS_OUTPUT.PUT_LINE('Looping The Content In The VARRAY Before TRIM!!!');
56
57 FOR j in 1..vr_var1.COUNT
58 LOOP
59 DBMS_OUTPUT.PUT_LINE('Index : '||j||' Value : '||vr_var1(j));
60 END LOOP;
61
62 nt_var1.TRIM(2);
63
64 DBMS_OUTPUT.PUT_LINE('Looping The Content In The NESTED TABLE After Removing Two Elements From End Using TRIM!!!');
65
66 FOR i in 1..nt_var1.COUNT
67 LOOP
68 DBMS_OUTPUT.PUT_LINE('Index : '||i||' Value : '||nt_var1(i));
69 END LOOP;
70
71 nt_var1.TRIM;
72
73 DBMS_OUTPUT.PUT_LINE('Looping The Content In The NESTED TABLE After Removing One Element From End Using TRIM!!!');
74
75 FOR i in 1..nt_var1.COUNT
76 LOOP
77 DBMS_OUTPUT.PUT_LINE('Index : '||i||' Value : '||nt_var1(i));
78 END LOOP;
79
80 nt_var1.TRIM(2);
81
82 DBMS_OUTPUT.PUT_LINE('COUNT Of NESTED TABLE After Removing Last Two Elements From End Using TRIM : '||nt_var1.COUNT);
83
84 FOR i in 1..vr_var1.COUNT
85 LOOP
86 vr_var1.TRIM;
87 END LOOP;
88
89 DBMS_OUTPUT.PUT_LINE('COUNT Of VARRAY After Removing All Elements From End Using TRIM : '||vr_var1.COUNT);
90
91 BEGIN
92 vr_var1.TRIM;
93 EXCEPTION
94 WHEN OTHERS THEN
95 DBMS_OUTPUT.PUT_LINE('Attempt To TRIM More Elements Than Actually Exist Raise : '||DBMS_UTILITY.FORMAT_ERROR_STACK);
96 END;
97
98 BEGIN
99 nt_var1.TRIM;
100 EXCEPTION
101 WHEN OTHERS THEN
102 DBMS_OUTPUT.PUT_LINE('Attempt To TRIM More Elements Than Actually Exist Raise : '||DBMS_UTILITY.FORMAT_ERROR_STACK);
103 END;
104 END;
105 /
--OUTPUT :::
COLLECTION_IS_NULL Predefined Exception Raised With TRIM Being Used With Uninitialized NESTED TABLE!!!
COLLECTION_IS_NULL Predefined Exception Raised With TRIM Being Used With Uninitialized VARRAY!!!
Looping The Content In The NESTED TABLE Before TRIM!!!
Index : 1 Value : 1
Index : 2 Value : 2
Index : 3 Value : 3
Index : 4 Value : 4
Index : 5 Value : 5
Looping The Content In The VARRAY Before TRIM!!!
Index : 1 Value : 6
Index : 2 Value : 7
Index : 3 Value : 8
Index : 4 Value : 9
Index : 5 Value : 10
Index : 6 Value : 11
Looping The Content In The NESTED TABLE After Removing Two Elements From End Using TRIM!!!
Index : 1 Value : 1
Index : 2 Value : 2
Index : 3 Value : 3
Looping The Content In The NESTED TABLE After Removing One Element From End Using TRIM!!!
Index : 1 Value : 1
Index : 2 Value : 2
COUNT Of NESTED TABLE After Removing Last Two Elements From End Using TRIM : 0
COUNT Of VARRAY After Removing All Elements From End Using TRIM : 0
Attempt To TRIM More Elements Than Actually Exist Raise : ORA-06533: Subscript beyond count
Attempt To TRIM More Elements Than Actually Exist Raise : ORA-06533: Subscript beyond count
If you use DELETE & TRIM on the same collection, you can get some very surprising results. Consider this scenario: if you DELETE an element at the END of a nested table variable and then do a TRIM on the same variable, how many elements have you removed?
You might think that you have removed two elements, but, in fact, you have removed only one. The placeholder that is left by DELETE is what TRIM acts upon. To avoid confusion, Oracle recommends using either DELETE or TRIM, but not both, on a given collection.
SQL> DECLARE
2 TYPE nt_tab1 IS TABLE OF NUMBER;
3 nt_var1 nt_tab1 := nt_tab1(1,2,3,4,5);
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE('Looping NESTED TABLE Before DELETE And TRIM Operation');
6
7 FOR i IN 1..nt_var1.COUNT
8 LOOP
9 DBMS_OUTPUT.PUT_LINE('Index : '||i||' Value - '||nt_var1(i));
10 END LOOP;
11
12 nt_var1.DELETE(5);
13 nt_var1.TRIM;
14
15 DBMS_OUTPUT.PUT_LINE('Looping NESTED TABLE After DELETE And TRIM Operation');
16
17 FOR i IN 1..nt_var1.COUNT
18 LOOP
19 DBMS_OUTPUT.PUT_LINE('Index : '||i||' Value - '||nt_var1(i));
20 END LOOP;
21 END;
22 /
--OUTPUT :::
Looping NESTED TABLE Before DELETE And TRIM Operation
Index : 1 Value - 1
Index : 2 Value - 2
Index : 3 Value - 3
Index : 4 Value - 4
Index : 5 Value - 5
Looping NESTED TABLE After DELETE And TRIM Operation
Index : 1 Value - 1
Index : 2 Value - 2
Index : 3 Value - 3
Index : 4 Value – 4
