PL/SQL Collection Methods

Oracle-collection-method

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

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 *