Oracle PL/SQL - TRIM Collection Method

Introduction

TRIM deletes elements from the end of a varray or nested table.

This method has these forms:

• TRIM removes one element from the end of the collection; it raises the predefined exception SUBSCRIPT_BEYOND_COUNT for empty collection.
• TRIM(n) removes n elements from the end of the collection; it raises the predefined exception SUBSCRIPT_BEYOND_COUNT for no enough element to remove.

TRIM operates on the internal size of a collection.

If DELETE deletes an element but keeps a placeholder for it, then TRIM considers the element to exist.

TRIM can delete a deleted element.

You cannot restore a trimmed element by assigning a valid value to it.

The following code declares a nested table variable and shows how to use TRIM and DELETE method.

Demo

SQL>
SQL>-- from  w  w w .j  av  a  2 s . c  om
SQL> CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER;
  2  /

Type created.

SQL> CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) IS
  2    i  NUMBER;
  3  BEGIN
  4    i := nt.FIRST;
  5
  6    IF i IS NULL THEN
  7      DBMS_OUTPUT.PUT_LINE('nt is empty');
  8    ELSE
  9      WHILE i IS NOT NULL LOOP
 10        DBMS_OUTPUT.PUT('nt.(' || i || ') = '); print(nt(i));
 11        i := nt.NEXT(i);
 12      END LOOP;
 13    END IF;
 14  END print_nt;
 15  /

Procedure created.

SQL>
SQL>
SQL> DECLARE
  2    nt nt_type := nt_type(11, 22, 33, 44, 55, 66);
  3  BEGIN
  4    print_nt(nt);
  5
  6    nt.TRIM;       -- Trim last element
  7    print_nt(nt);
  8
  9    nt.DELETE(4);  -- Delete fourth element
 10    print_nt(nt);
 11
 12    nt.TRIM(2);    -- Trim last two elements
 13    print_nt(nt);
 14  END;
 15  /
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(6) = 66
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(4) = 44
nt.(5) = 55
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33
nt.(5) = 55
nt.(1) = 11
nt.(2) = 22
nt.(3) = 33

PL/SQL procedure successfully completed.

SQL>

• Previous
• Next