Welcome back to the last tutorial in the PL/SQL Collection series. The longest video tutorial series so far on the channel. In this tutorial we will learn about the last PL/SQL Collection procedure which is TRIM procedure in Oracle Database.

What is PL/SQL Collection Method TRIM?

Collection Method TRIM in Oracle db is an overloaded procedure using which you can remove one or more elements from the end of a collection.

In how many ways can we call the collection method TRIM procedure in Oracle Database?

As PL/SQL Collection Method TRIM is an Overloaded procedure thus we can call it in two different ways. These two different calls of TRIM procedure are:

1. TRIM: Trim procedure without parameter.

If we use TRIM procedure without any parameters then it will remove only one element from the end of the collection.

2. TRIM: Trim procedure with one parameter.

TRIM procedure called by passing one argument will remove number of elements from the end of the collection as specified by the parameter. That parameter must be a valid Integer number. Also it should not be greater than the maximum number of elements that your collection has.

Info: Trim procedure will raise an error if there is an attempt to trim space below zero elements.

Can we use PL/SQL collection method TRIM with all the three types of collections?

Unfortunately no, we cannot. Similar to PL/SQL Collection procedure EXTEND, procedure TRIM can only be used with collection Nested Tables and VARRAYs. However we cannot use it with Associative Arrays.

What will happen if we use PL/SQL Collection Procedure TRIM with an Associative array?

If collection method Trim of Oracle Database versions is used with an Associative Array then you will get a compile time error.

What is the specification of Trim Procedure in Oracle Database?

The specification of collection procedure TRIM is:

PROCEDURE TRIM (n PLS_INTEGER:= 1);

If n is NULL then trim will do nothing.

I heard you saying in the video that we can also get SUBSCRIPT_BEYOND_COUNT exception?

Yes, PL/SQL Collection Method TRIM will raise SUBSCRIPT_BEYOND_COUNT exception. If there is an attempt to trim more elements than actually exists in the collection.

You can find the example of this exception which I demonstrated in the video later in this blog. If you want to watch the video, then here it is. If not then feel free to scroll down.

Are there any other exceptions associated with PL/SQL Collection method Trim which we should know about?

Yes, there is one more exception associated with TRIM procedure and that is COLLECTION_IS_NULL exception.

Whenever you apply collection procedure TRIM to an uninitialized Nested Table or VARRAY then the compiler will raise COLLECTION_IS_NULL exception.

Can we use TRIM and DELETE procedure together?

No we cannot use collections TRIM and DELETE procedure together. Using them with each other will produce unexpected results.

Think about a scenario when you have DELETED an element situated at the end of a VARRAY variable and thereafter apply TRIM procedure on the same. Can you guess the number of elements that you may have removed? You might be confused into believing that two elements have been removed however the fact is that only one has been removed. TRIM acts upon the placeholder left by procedure DELETE.

In order to steer clear of these confusing and unnerving results, Oracle database support highly recommends that these two procedures should be used exclusively on a given collection.

What about the examples? Are we doing them in this blog or not?

Sure, we will definitely do the examples in this blog. In fact I was about to show you the demonstration of each of PL/SQL Collection procedure TRIM call that we mentioned above. Here we go:

1. PL/SQL Collection Procedure TRIM without parameter.

SET SERVEROUTPUT ON;
DECLARE
    TYPE my_nestedTable IS TABLE OF number;
    nt_obj  my_nestedTable := my_nestedTable(1,2,3,4,5);
BEGIN
    nt_obj.TRIM;
    DBMS_OUTPUT.PUT_LINE ('After TRIM procedure');
    FOR i IN 1..nt_obj.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE (nt_obj (i));
    END LOOP;
END;
/ 

2. Collection Procedure TRIM with parameter.

SET SERVEROUTPUT ON;
DECLARE
    TYPE my_nestedTable IS TABLE OF number;
    nt_obj  my_nestedTable := my_nestedTable(1,2,3,4,5);
BEGIN
    nt_obj.TRIM (3);
    DBMS_OUTPUT.PUT_LINE ('After TRIM procedure');
    FOR i IN 1..nt_obj.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE (nt_obj(i));
    END LOOP;
END;
/

3. PL/SQL Collection Procedure TRIM SUBSCRIPT_BEYOND_COUNT exception.

SET SERVEROUTPUT ON;
DECLARE
    TYPE my_nestedTable IS TABLE OF number;
    nt_obj  my_nestedTable := my_nestedTable(1,2,3,4,5);
BEGIN
    nt_obj.TRIM(6);
    DBMS_OUTPUT.PUT_LINE ('After TRIM procedure');
    FOR i IN 1..nt_obj.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE (nt_obj(i));
    END LOOP;
END;
/

SUBSCRIPT_BEYOND_COUNT exception occurs when we pass an argument greater than the total number of elements of the collection. In the above example the collection which we use is Nested table with the name ‘my_nestedTable’ which has 5 numeric elements stored into it. However in the procedure call we instructed the compiler to TRIM 6 elements which is definitely impossible thus in this case compiler raises SUBSCRIPT_BEYOND_COUNT exception.

4. Collection Procedure TRIM with VARRAY.

SET SERVEROUTPUT ON;
DECLARE
 TYPE inBlock_vry IS VARRAY (5) OF NUMBER;
 vry_obj inBlock_vry := inBlock_vry(1, 2, 3, 4, 5);
BEGIN
    --TRIM without parameter
    vry_obj.TRIM;
    DBMS_OUTPUT.PUT_LINE ('After TRIM procedure');
    FOR i IN 1..vry_obj.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE (vry_obj(i));
    END LOOP;
    --TRIM with Parameter
    vry_obj.TRIM (2);
    DBMS_OUTPUT.PUT_LINE ('After TRIM procedure');
    FOR i IN 1..vry_obj.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE (vry_obj(i));
    END LOOP;
END;
/

That is the tutorial explaining the concepts of PL/SQL Collection Method TRIM procedure in Oracle Database.

Hope you liked it. Do connect with me on my Facebook Page for more updates and insights on Oracle Database concepts. Thanks & have a great day!