pl/sql collection method extend in oracle database by manish sharma

After PL/SQL Collection Method DELETE, EXTEND procedure in Oracle database is the second one on 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 EXTEND procedure in Oracle Database?

PL/SQL Collection Procedure is an overloaded procedure. Therefore, it means that we call this same procedure in different ways. These different ways for calling Collection Procedure EXTEND are –

  1. 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.

  1. Extend (n): Extend procedure call with one argument.

Collection procedure Extend with one argument will append the number of NULL elements that you mentioned as the argument of the procedure. But, remember that the argument must be a valid Integer value.

  1. 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. Moreover the second argument is the index number. Moreover it’s value will be copied and assigned to each of the newly appended elements 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. Furthermore, EXTEND cannot be used with collection Associative Arrays.

Can you show us the specification of Collection Method EXTEND procedure in Oracle Database?

Sure, why not! Here are the overloaded specifications of PL/SQL Collection Method EXTEND —

EXTEND Procedure with one argument:

PROCEDURE EXTEND (n pls_integer := 1);

Collection Method EXTEND with two arguments:

PROCEDURE EXTEND (n pls_integer, v pls_integer);

When should we use collection method EXTEND in our code?

When you have a collection (either Nested Table or VARRAY) in your code which is not initialized with sufficient number of elements. In that case 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. Consequently, 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?

Yes, we will definitely do the demonstration of each of the PL/SQL Collection procedure EXTEND call that we mentioned above. In addition to the Extend Procedure Call with VARRAY.

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;
/

2. 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;
/

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;
/

4. 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.

Hope we have discussed all the possible questions on PL/SQL Collection Method EXTEND which you could face in your Oracle db Certification Exam as well as in your Interview. Also, if you have any confusions about Certifications then you can refer to our Oracle Database Certification Exam Guide.

RELATED ARTICLESMORE FROM AUTHOR