Exploring PL/SQL Collection Methods: DELETE, TRIM, and Their Best Practices
Collection Methods
They are called methods because they use member‑method syntax, unlike functions or procedures that require passing collections as parameters. The syntax is similar to other programming languages, such as JavaScript.
COUNT function
- Returns the current number of elements in a collection.
Wrong syntax
COUNT(book_table)Correct syntax
book_table.COUNTNote: Collection methods are available only in PL/SQL, not within SQL.
While this article does not list every collection method, we will focus on the two that have particularly interesting behaviors: DELETE and TRIM.
DELETE Method
Syntax
| Form | Description |
|---|---|
DELETE (without any arguments) | Removes all elements from the collection. |
DELETE(i) | Removes the i‑th element from a nested table or associative array. |
DELETE(i, j) | Removes all elements in an inclusive range starting from i and ending at j. |
Important note: When you use parameters with DELETE, a placeholder is kept for the “removed” element, so the collection’s index structure remains intact.
TRIM Method
Do not use
DELETEandTRIMon the same collection.
PL/SQL offers powerful and useful methods, but it’s important to pay close attention to how each one is used. The TRIM method removes trailing elements from a collection, while DELETE removes specific elements (or all elements) and leaves gaps. Mixing the two on the same collection can lead to unexpected behavior.