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:
/*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.
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,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 EXTEND method. 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:
/*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.
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 defined for NULL value of n.
The following PL/SQL block shows the operation of the TRIM method on an
initialized PL/SQL table collection type:
/*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.
Like other methods, the TRIM method raises a COLLECTION_IS_NULL exception for
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. The DELETE 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:
• 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 declares a coll_method_demo_t collection along with
its collection variable. This program displays the first element of the collection before and after the deletion of the first subscript:
/*Enable the SERVEROUTPUT on to display the output*/ SET SERVEROUTPUT 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
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:
/*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
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.