PL/SQL collection methods

Oracle provides a set of methods which can be used in conjunction with collections in PL/SQL blocks. These methods access a collection type variable and perform relevant activities such as extension, trimming, and deleting collection elements. Besides these activities, few methods also provide information about the collection such as COUNT and EXISTS. These utilities are known as collection methods and they are not a built-in subprogram, because they can be used exclusively in conjunction with collections.

The common syntax for all the collection methods is as follows:

[sourcecode language=”sql”]
[COLLECTION].METHOD (PARAMETERS)
[/sourcecode]

EXISTS

The EXISTS function checks the existence of an element in a collection. The general syntax of this function is EXISTS(<index>). It takes the subscript as an input argument and searches it in the collection. If the element corresponding to the index is found, it returns TRUE or else, returns FALSE. It is the only method which doesn’t raise any exception during its application with an uninitialized collection.

The following PL/SQL block declares a local nested table collection and its two variables. While one array is uninitialized, the other one is initialized with sample data. We will check the existence of the first element in both arrays:

[sourcecode language=”sql”]
/*Enable the SERVEROUTPUT on to display the output*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare a local nested table collection*/
TYPE coll_method_demo_t IS TABLE OF NUMBER;
/*Declare collection type variables*/
L_ARRAY1 coll_method_demo_t;
L_ARRAY2 coll_method_demo_t := coll_method_demo_t (45,87,57);
BEGIN
/*Check if first cell exists in the array 1*/
IF L_ARRAY1.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE(‘Element 1 found in Array 1’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Element 1 NOT found in Array 1’);
END IF;
/*Check if first cell exists in the array 2*/
IF L_ARRAY2.EXISTS(1) THEN
DBMS_OUTPUT.PUT_LINE(‘Element 1 found in Array 2’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Element 1 NOT found in Array 2’);
END IF;
END;
/

Element 1 NOT found in Array 1
Element 1 found in Array 2

PL/SQL procedure successfully completed.
[/sourcecode]

COUNT

As the name suggests, the COUNT function counts the number of elements in an initialized collection. The COUNT method raises the COLLECTION_IS_NULL exception for uninitialized collections.

The COUNT function returns zero when:

• A nested table or varray collection is initialized with an empty collection
• An associative array doesn’t have any elements

It can be operated upon all three types of collections. The following PL/SQL block declares a local nested table collection and its two variables. We will check the element count in both the collection variables:

[sourcecode language=”sql”]
/*Enable the SERVEROUTPUT on to display the output*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare the local collection type*/
TYPE coll_method_demo_t IS TABLE OF NUMBER;
/*Declare the collection variables and initialize them with test
data*/
l_loc_var coll_method_demo_t := coll_method_demo_t (10,20,30);
BEGIN
DBMS_OUTPUT.PUT_LINE(‘The array size is ‘||l_loc_var.count);
END;
/

The array size is 3

PL/SQL procedure successfully completed.
[/sourcecode]

LIMIT

The LIMIT function returns the maximum number of elements that can be accommodated by a VARRAYcollection type variable. This method can be used with VARRAY collection types only. The LIMIT method raises the COLLECTION_IS_NULL exception for uninitialized collections.

For associative arrays and nested tables, the LIMIT method returns NULL.

The following PL/SQL block declares a local varray type and a variable of its type. The varray type variable has been initialized with test data. Observe the difference between the COUNT and LIMIT methods:

[sourcecode language=”sql”]
/*Enable the SERVEROUTPUT on to display the output*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare local varray and its variable*/
TYPE coll_method_demo_v IS VARRAY(10) OF NUMBER;
L_ARRAY1 coll_method_demo_v := coll_method_demo_v (10,20,30);BEGIN
/*Display the current count*/
DBMS_OUTPUT.PUT_LINE(‘The varray has ‘||L_ARRAY1.COUNT||’
elements’);
/*Display the maximum limit*/
DBMS_OUTPUT.PUT_LINE(‘The varray can hold ‘||L_ARRAY1.LIMIT||’
elements’);
END;
/

The varray has 3 elements
The varray can hold 10 elements

PL/SQL procedure successfully completed.
[/sourcecode]

FIRST and LAST

The FIRST and LAST functions return the first and last subscripts of a collection. For an empty collection, these methods return NULL value. These methods can be used with all three types of collections. TheFIRST and LAST methods raise exception COLLECTION_IS_NULL for uninitialized collections.

The following PL/SQL block demonstrates the use of the FIRST and LAST methods with an initialized collection:

[sourcecode language=”sql”]
/*Enable the SERVEROUTPUT on to display the output*/
SET SERVEROUTPUT ON
/*Start the PL/SQL bock*/
DECLARE
/*Display a local nested table collection*/
TYPE coll_method_demo_t IS TABLE OF NUMBER;
L_ARRAY coll_method_demo_t := coll_method_demo_t (10,20,30);
BEGIN
/*Display the first and last elements*/
DBMS_OUTPUT.PUT_LINE(‘First element of the array: ‘|| L_ARRAY (L_
ARRAY.FIRST));
DBMS_OUTPUT.PUT_LINE(‘Last element of the array: ‘|| L_ARRAY (L_
ARRAY.LAST));
END;
/

Starting Index of the array: 10
Last Index of the array: 30

PL/SQL procedure successfully completed.
[/sourcecode]

PRIOR and NEXT

The PRIOR and NEXT functions take an input index and return its previous and next index from the given collection. If the PRIOR and NEXT functions are used with the first and last indexes respectively, the method returns NULL.

Both the methods can be used with all three types of collections. The PRIOR and NEXT methods raise exception COLLECTION_IS_NULL for uninitialized collections.

The following PL/SQL shows the usage of the PRIOR and NEXT methods with a PL/SQL type collection:

[sourcecode language=”sql”]
/*Enable the SERVEROUTPUT on to display the output*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare a local nested table collection*/
TYPE coll_method_demo_t IS TABLE OF NUMBER;
L_ARRAY coll_method_demo_t := coll_method_demo_t
(10,20,30,100,48,29,28);
BEGIN
/*Display the element which appears before 5th index*/
DBMS_OUTPUT.PUT_LINE(‘Element before 5th element: ‘||L_ARRAY(L_
ARRAY.PRIOR(5)));
/*Display the element which appears after 6th index*/
DBMS_OUTPUT.PUT_LINE(‘Element after 6th element: ‘||L_ARRAY(L_ARRAY.
NEXT(6)));
END;
/

Element before 5th element: 100
Element after 6th element: 28

PL/SQL procedure successfully completed.
[/sourcecode]

EXTEND

The EXTEND function is used to append elements to a collection variable of nested table or varray type. It cannot be used with associative arrays.

It is an overloaded function which can be used in three signatures as follows:

• EXTEND: It appends the collection with a NULL element
• EXTEND(x): It appends the collection with x number of NULL elements
• EXTEND(x,y): It appends the collection with x elements and with the value as that of the y element. If the y element doesn’t exist, the system raises a SUBSCRIPT_BEYOND_COUNT exception.

The following PL/SQL block demonstrates the extension using all three signatures of the EXTENDmethod. The first extension appends the fourth NULL element to the array. The second extension appends the fifth and sixth NULL elements to the array. The third extension appends the seventh and eighth elements as 10 (value of the first element) to the array:

[sourcecode language=”sql”]
/*Enable the SERVEROUTPUT on to display the output*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare local nested table collection type*/
TYPE coll_method_demo_t IS TABLE OF NUMBER;
/*Declare collection type variable and initialize it*/
L_ARRAY coll_method_demo_t := coll_method_demo_t (10,20,30);
BEGIN
/*Extend the collection. It adds a NULL element to the collection*/
L_ARRAY.EXTEND;
DBMS_OUTPUT.PUT_LINE(L_ARRAY.LAST||’ element of the array is =
‘||L_ARRAY(L_ARRAY.LAST));
/*Extend the collection. It adds two NULL elements at the end of the
collection*/
L_ARRAY.EXTEND(2);
DBMS_OUTPUT.PUT_LINE(L_ARRAY.LAST||’ element of the array is =
‘||L_ARRAY(L_ARRAY.LAST));
/*Extend the collection. It adds two NULL elements at the end of the
collection and populates with the 1st element*/
L_ARRAY.EXTEND(2,1);
DBMS_OUTPUT.PUT_LINE(L_ARRAY.LAST||’ element of the array is =
‘||L_ARRAY(L_ARRAY.LAST));
END;
/

L_ARRAY(4) element of the array is =
L_ARRAY(6) element of the array is =
L_ARRAY(8) element of the array is = 10

PL/SQL procedure successfully completed.
[/sourcecode]

The EXTEND method raises the COLLECTION_IS_NULL exception for uninitialized collections. If a varray is attempted for extension beyond its maximum allowed limit, Oracle raises aSUBSCRIPT_BEYOND_LIMIT exception.

TRIM

The TRIM function is used to cut the elements from the specified collection, of the nested table or varray type. It cannot be used with associative array type collections. TRIM is an overloaded method, which can be used in the following two signatures:

• TRIM: It trims one element from the end of the collection
• TRIM(n): It trims n elements from the end of the collection. If n exceeds the total count of elements in the collection, the system raises a SUBSCRIPT_ BEYOND_COUNT exception. No action has been de? ned for NULL value of n.
  • DELETE: It flushes out all the elements of a collection
  • DELETE(n): It deletes the nth index from the collection
  • DELETE(n,m): It performs range deletion, where all the elements within the range of the subscripts n and m are deleted

The following PL/SQL block shows the operation of the TRIM method on an initialized PL/SQL table collection type:

[sourcecode language=”sql”]
/*Enable the SERVEROUTPUT on to display the output*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare a local nested table collection type*/
TYPE coll_method_demo_t IS TABLE OF NUMBER;
/*Declare a collection variable and initialize it*/
L_ARRAY coll_method_demo_t := coll_method_demo_t (10,20,30,40,50);
BEGIN
/*Trim the last element of the collection*/
L_ARRAY.TRIM;
DBMS_OUTPUT.PUT_LINE(‘L_ARRAY(‘||L_ARRAY.LAST||’) element is =
‘||L_ARRAY(L_ARRAY.LAST));
/*Trim the last 2 elements of the collection*/
L_ARRAY.TRIM(2);
DBMS_OUTPUT.PUT_LINE(‘L_ARRAY(‘||L_ARRAY.LAST||’) element is =
‘||L_ARRAY(L_ARRAY.LAST));
END;
/

L_ARRAY(4) element is = 40
L_ARRAY(2) element is = 20

PL/SQL procedure successfully completed.
[/sourcecode]

Like other methods, the TRIM method raises a COLLECTION_IS_NULL exception for uninitialized collections.

DELETE

The DELETE function is used to delete elements from a given collection. The DELETE operation leaves the collection sparse. Any reference to the deleted index would raise a NO_DATA_FOUND exception. TheDELETE method raises a COLLECTION_IS_NULL exception for uninitialized collections. It can be used with all three types of collections.

The overloaded method can be used in the following signatures:

The following PL/SQL block declares a coll_method_demo_t collection along with its collection variable. This program displays the ? rst element of the collection before and after the deletion of the first subscript:

[sourcecode language=”sql”]
/*Enable the SERVEROUTPUT on to display the output*/
SET SERVERO UTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare the local nested table collection*/
TYPE coll_method_demo_t IS TABLE OF NUMBER;
/*Declare a collection variable and initialize it*/
L_ARRAY coll_method_demo_t := coll_method_demo_t (10,20,30,40,50);
BEGIN
DBMS_OUTPUT.PUT_LINE(‘First element before deletion is :L_
ARRAY(‘||L_ARRAY.FIRST||’) = ‘||L_ARRAY(L_ARRAY.FIRST));
/*Delete the 1st element from the collection*/
L_ARRAY.DELETE(1);
DBMS_OUTPUT.PUT_LINE(‘First element after deletion is : L_
ARRAY(‘||L_ARRAY.FIRST||’) = ‘||L_ARRAY(L_ARRAY.FIRST));
END;
/

First element before deletion is : L_ARRAY(1) = 10
First element after deletion is : L_ARRAY(2) = 20

PL/SQL procedure successfully completed.
[/sourcecode]

Interestingly, Oracle doesn’t allow the deletion of individual elements in a varray collection. Either all the elements of the varray have to be removed using the VARRAY.DELETE method or the elements can be trimmed from the end of the varray collection. This scenario is illustrated in the following program:

[sourcecode language=”sql”]
/*Enable the SERVEROUTPUT on to display the output*/
SET SERVEROUTPUT ON
/*Start the PL/SQL block*/
DECLARE
/*Declare the local varray collection*/
TYPE coll_method_demo_t IS VARRAY (10) OF NUMBER;
/*Declare a collection variable and initialize it*/
L_ARRAY coll_method_demo_t := coll_method_demo_t (10,20,30,40,50);
BEGIN
/*Delete the second element of varray*/
L_ARRAY.DELETE(2);
END;
/

L_ARRAY.DELETE(2);
*
ERROR at line 8:
ORA-06550: line 8, column 3:
PLS-00306: wrong number or types of arguments in call to ‘DELETE’
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
[/sourcecode]

It is recommended that the TRIM and DELETE methods must not be operated together or simultaneously on a collection. The DELETE method retains a placeholder for the deleted element, while the TRIM method destroys the element from the collection. Therefore, the operation sequence “DELETE(last) followed by TRIM(1)” would result in removal of a single element only.

In this article, Saurabh Gupta looked at: PL/SQL Collections: Overview, Associative arrays, Nested tables, Varray and PL/SQL collection methods.

This article is based on his book Oracle Advanced PL/SQL Developer Professional Guide released in May 2012 by Packt Publishing. Click to browse book details & buy.