Oracle Collection API โ Methods and Exception Handling in PL/SQL
Oracle provides a robust Collection API that enables developers to efficiently interact with and manipulate collection types such as VARRAYs, nested tables, and associative arrays. Initially introduced in Oracle 8i and significantly enhanced in later versions, the Collection API offers a set of built-in functions and procedures that simplify operations such as accessing, modifying, and traversing collections.
Although earlier versions of PL/SQL allowed limited collection manipulation, Oracleโs introduction of associative arrays necessitated mastery of the Collection API, particularly the FIRST, LAST, NEXT, and PRIOR methodsโessential for navigating collections indexed by strings.
Overview of Oracle Collection API Methods
The Collection API includes both functions (which return values) and procedures (which perform actions without returning values). Below is a categorized summary of commonly used methods.
Collection Methods โ Summary Table
| Method | Description | Return Type | Applicable To |
|---|---|---|---|
| COUNT | Returns the current number of elements in the collection. May be less than LIMIT in varrays. | PLS_INTEGER | All |
| DELETE(n) | Removes the element at index n. | None | All |
| DELETE(n, m) | Removes a range of elements from index n to m, inclusive. | None | All |
| EXISTS(n) | Returns TRUE if an element exists at index n, otherwise FALSE. Does not raise exceptions on NULL. | BOOLEAN | All |
| EXTEND | Adds a new uninitialized element to the end of the collection. | None | VARRAY, Nested Table |
| EXTEND(n) | Appends n new uninitialized elements. | None | VARRAY, Nested Table |
| EXTEND(n, i) | Appends n new elements, each a copy of the i-th element. | None | VARRAY, Nested Table |
| FIRST | Returns the lowest index value currently in use. | Index type | All |
| LAST | Returns the highest index value currently in use. | Index type | All |
| LIMIT | Returns the maximum number of elements allowed in a varray. | PLS_INTEGER | VARRAY |
| NEXT(n) | Returns the next higher index after n; returns NULL if none exists. | Index type | All |
| PRIOR(n) | Returns the next lower index before n; returns NULL if none exists. | Index type | All |
| TRIM | Removes the last element from the collection. | None | VARRAY, Nested Table |
| TRIM(n) | Removes the last n elements from the collection. | None | VARRAY, Nested Table |
Standard Exceptions in Collection Handling
Proper use of collection methods requires awareness of common exceptions that may arise during runtime. Oracle defines the following standard exceptions specific to collections:
| Exception | Raised When |
|---|---|
COLLECTION_IS_NULL | Attempting to access or modify a collection that has not been initialized. |
NO_DATA_FOUND | Accessing a deleted or non-existent subscript (e.g., in associative arrays with string keys). |
SUBSCRIPT_BEYOND_COUNT | Referencing an index greater than the collection’s current element count (varrays, nested tables). |
SUBSCRIPT_OUTSIDE_LIMIT | Using an index outside the allowable range defined by LIMIT (for varrays only). |
VALUE_ERROR | Assigning a value incompatible with PLS_INTEGER for numeric indexes. |
Practical Notes
The
EXISTS(n)method is the only one that safely operates on uninitialized collections without raising an exception.Methods like
NEXT,PRIOR,FIRST, andLASTare critical when working with sparse or string-indexed associative arrays.While
EXTEND,TRIM, andDELETEare procedures, others likeCOUNT,LIMIT,FIRST,LAST,NEXT, andPRIORare functions and return values for evaluation.
AmantPoint Exclusive Learning Series
ยฉ 2025 AmantPoint. All rights reserved. Educational content for developers and learners.
