After PL/SQL Collection Method DELETE, EXTEND is the second collection procedure in the list. We have used this procedure time and again but never got the chance to explore it in-detail. Thus I’ve decided to dedicate the whole blog to this Collection method. So sit back and enjoy the blog.
If you are new to PL/SQL and don’t know what are Procedures then we have a tutorial for you. Click here and learn all about PL/SQL procedures in Oracle Database.
What is PL/SQL Collection Method EXTEND?
Similar to DELETE, Collection method EXTEND is an overloaded PL/SQL procedure which is used for appending elements to the collection.
In how many ways we can call collection method EXTEND?
As PL/SQL Collection Procedure is an overloaded procedure, hence it means that we call this same procedure in different ways. These different ways for calling Collection Procedure EXTEND are –
- Extend: Extend procedure call without any argument.
Calling PL/SQL Collection procedure Extend without any argument will append a single NULL element to the collection.
- Extend (n): Extend procedure call with one argument.
Calling PL/SQL Collection procedure Extend with one argument will append the number of NULL elements that you mentioned as the argument of the Extend procedure. Remember that the argument must be a valid Integer value.
- Extend (n, v): Extend procedure call with two arguments.
In this case the first argument indicates the number of elements that will be appended to the collection. The second argument is the index number whose value will be copied and assigned to each of the newly appended element of the collection. This form of EXTEND is required for collections with “not null elements”.
Can we use PL/SQL Collection Method EXTEND with all three types of collections?
No, Collection Method EXTEND can only be applied to collection Nested Tables and VARRAYs. EXTEND cannot be used with collection Associative Arrays.
Can you show us the specification of Collection Method EXTEND?
Sure, why not! Here are the overloaded specifications of PL/SQL Collection Method EXTEND —
EXTEND with one argument:
PROCEDURE EXTEND (n pls_integer := 1);
EXTEND with two arguments:
PROCEDURE EXTEND (n pls_integer, v pls_integer);
When should we use collection method EXTEND in our code?
If you have a collection (either Nested Table or VARRAY) in your code which is not initialized with sufficient number of elements then you must first use PL/SQL Collection Method EXTEND.
What is the requirement of PL/SQL collection Method EXTEND?
Declaring, Defining and Initializing are the three steps which we have to go through while working with collection in Oracle Database but before storing the data into the index we need to create a memory slot for it. PL/SQL Collection procedure EXTEND helps us in creating that memory slot for that data.
What if we have deleted or trimmed the end of the collection?
In that case PL/SQL Collection method EXTEND will skip the deleted elements when it assigns a new index.
What if I apply the collection method EXTEND to an uninitialized Nested table or VARRAY?
If PL/SQL Collection Method EXTEND is applied to an uninitialized collection then it will show a COLLECTION_IS_NULL exception.
And what if I try to EXTEND a VARRAY beyond its defined limit?
If collection method EXTEND is used with VARRAY to extend it beyond its defined limit then you will have to face another exception which is SUBSCRIPT_BEYOND_LIMIT.
Hey, Manish! Will we ever see an example of this Collection Method EXTEND or not?
Yes, we will definitely do the demonstration of each of the PL/SQL Collection procedure EXTEND call that we mentioned above as well as the Extend Procedure Call with VARRAY.
Example 1: PL/SQL Collection Procedure EXTEND with No Argument.
SET SERVEROUTPUT ON; DECLARE TYPE my_nestedTable IS TABLE OF number; nt_obj my_nestedTable := my_nestedTable(); BEGIN nt_obj.EXTEND; nt_obj(1) := 10; DBMS_OUTPUT.PUT_LINE ('Data at index 1 is '||nt_obj(1)); END; /
Example 2: PL/SQL Collection Procedure EXTEND with One Argument.
SET SERVEROUTPUT ON; DECLARE TYPE my_nestedTable IS TABLE OF number; nt_obj my_nestedTable := my_nestedTable(); BEGIN nt_obj.EXTEND(3); nt_obj(1) := 10; nt_obj(2) := 20; nt_obj(3) := 30; DBMS_OUTPUT.PUT_LINE ('Data at index 1 is '||nt_obj(1)); DBMS_OUTPUT.PUT_LINE ('Data at index 2 is '||nt_obj(2)); DBMS_OUTPUT.PUT_LINE ('Data at index 3 is '||nt_obj(3)); END; /
Example 3: PL/SQL Collection Procedure EXTEND with Two Arguments.
SET SERVEROUTPUT ON; DECLARE TYPE my_nestedTable IS TABLE OF number; nt_obj my_nestedTable := my_nestedTable(); BEGIN nt_obj.EXTEND; nt_obj(1) := 28; DBMS_OUTPUT.PUT_LINE ('Data at index 1 is '||nt_obj(1)); nt_obj.EXTEND(5,1); DBMS_OUTPUT.PUT_LINE ('Data at index 4 is '||nt_obj(4)); END; /
Example 4: PL/SQL Collection Procedure EXTEND (No Argument) with VARRAY
SET SERVEROUTPUT ON; DECLARE TYPE my_Varray IS VARRAY (5) OF NUMBER; vry_obj my_Varray := my_Varray(); BEGIN vry_obj.EXTEND; vry_obj(1) := 10; DBMS_OUTPUT.PUT_LINE('Data at index 1 is '||vry_obj(1)); END; /
Each LOC of all the above (Except the 4th one) is explained in detail in the Video Tutorial on our YouTube channel which you can watch by clicking here.
Hope we have discussed all the possible questions on PL/SQL Collection Method EXTEND which you could face in your Certification Exam as well as in your Interview. If you have any confusions about Certification then you can refer to our Oracle Database Certification Exam Guide.