TRIM Method in PL/SQL – Remove Elements from Collections

The TRIM Method in PL/SQL is a collection API procedure that removes elements from the end of a collection, typically from a varray or nested table. This method is overloaded, meaning it has multiple versions with different behaviors depending on the parameter usage.

Understanding the TRIM Method in PL/SQL is essential for controlling memory and element count when manipulating PL/SQL collections.

What is the TRIM Method in PL/SQL?

The TRIM method deallocates one or more elements from the end of a collection. It is especially useful for reducing the size of a varray or nested table.

Supports two forms: TRIM and TRIM(n)
Works on varrays and nested tables
Not supported on associative arrays

Syntax of TRIM Method in PL/SQL

collection_name.TRIM              -- Removes the last element
collection_name.TRIM(n)          -- Removes the last n elements

Example: TRIM Method in PL/SQL

DECLARE
   -- Define a varray type
   TYPE number_varray IS VARRAY(5) OF INTEGER;
   number_list number_varray := number_varray(1,2,3,4,5);

   -- Local procedure to print collection
   PROCEDURE print_list(list_in number_varray) IS
   BEGIN
      FOR i IN 1..list_in.COUNT LOOP
         DBMS_OUTPUT.PUT_LINE('List Index ['||i||'] List Value ['||list_in(i)||']');
      END LOOP;
   END print_list;

BEGIN
   -- Initial state
   DBMS_OUTPUT.PUT_LINE('Varray after initialization');
   DBMS_OUTPUT.PUT_LINE('–––––––––––––––––');
   print_list(number_list);

   -- Remove 1 element
   number_list.TRIM;
   DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Varray after single element trim');
   DBMS_OUTPUT.PUT_LINE('–––––––––––––––––');
   print_list(number_list);

   -- Remove 3 elements
   number_list.TRIM(3);
   DBMS_OUTPUT.PUT_LINE(CHR(10) || 'Varray after three element trim');
   DBMS_OUTPUT.PUT_LINE('–––––––––––––––––');
   print_list(number_list);
END;
/

Output:

Varray after initialization
–––––––––––––––––
List Index [1] List Value [1]
List Index [2] List Value [2]
List Index [3] List Value [3]
List Index [4] List Value [4]
List Index [5] List Value [5]

Varray after single element trim
–––––––––––––––––
List Index [1] List Value [1]
List Index [2] List Value [2]
List Index [3] List Value [3]
List Index [4] List Value [4]

Varray after three element trim
–––––––––––––––––
List Index [1] List Value [1]

Explanation of TRIM Method

Form	Description
`TRIM`	Removes the last element of the collection
`TRIM(n)`	Removes the last n elements of the collection
Exception	`SUBSCRIPT_BEYOND_COUNT` if trimming below zero

Best Practices

Always check the COUNT before calling TRIM(n)
Avoid trimming beyond available elements to prevent runtime errors
Use TRIM when managing dynamic data in varrays or nested tables

AmantPoint Exclusive Learning Series
© 2025 AmantPoint. All rights reserved. Educational content for developers and learners.