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

FormDescription
TRIMRemoves the last element of the collection
TRIM(n)Removes the last n elements of the collection
ExceptionSUBSCRIPT_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

Previous
Next

๐Ÿ“˜ AmantPoint Exclusive Learning Series
ยฉ 2025 AmantPoint. All rights reserved. Educational content for developers and learners.

Facebooktwitterredditpinterestlinkedinmailby feather
Scroll to Top