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

DELETE Procedure In Oracle Database

As discussed in the Introduction to collection methods we have seven collection functions and 3 collection procedures. Therefore in total we have 10 collection methods among which we have already discussed 7 collection functions so far. Thus today in this tutorial we will discuss the first PL/SQL collection procedure which is DELETE procedure in Oracle Database.

What is PL/SQL collection method Delete?

Collection method DELETE is an overloaded procedure which removes elements from the collection.

What do you mean by an overloaded procedure?

You heard it right. PL/SQL collection method DELETE is an overloaded procedure. Which means you can use the same procedure in three different ways. These three different ways are –

  • DELETE: Simple procedure call without any parameters. Thus if the PL/SQL Collection procedure DELETE is used without any parameter then it will remove all the elements from the collection.
  • DELETE (index-number): Procedure call with a single parameter. This single parameter is the valid index number of the collection. Collection procedure DELETE called by passing a valid index number will remove the element of the specific index.
  • DELETE (start-index, ending-index): Procedure call with two parameters. This way of calling a DELETE procedure is termed as Range delete. In this way you have to specify two Indexes. And the procedure deletes the range of elements which fall between starting-index and ending-index.

If the collection is a string-indexed associative array the starting-index and ending-index are string; otherwise starting and ending indexes are integers.

Can we use DELETE Procedure in Oracle Database with all the collections?

Yes, collection method DELETE can be used will all three types of collections. These are – Nested table, VARRAYs and Associative arrays.

Wait! But if we use the procedure DELETE with VARRAYs then won’t it make a sparse collection? 

As VARRAY is not a sparse collection hence we cannot delete individual rows from it. Furthermore, the only procedure call that we can execute with VARRAY is the first one. Which is collection method DELETE without any arguments which removes all the elements from the collection. The only way of removing an individual row from a VARRAY is by trimming it from its end using another procedure call TRIM.

Can we expect any exception with collection method DELETE?

Yes, there is an exception associated with PL/SQL collection method DELETE. If the procedure DELETE is applied to an uninitialized Nested Table and VARRAY then it raises a “Collection_is_Null” exception.  

Examples of PL/SQL Collection Method DELETE.

Let’s see some examples of each of the above mentioned Procedure DELETE calls.

Example 1: Simple procedure call without argument.

DECLARE
    TYPE my_nested_table IS
        TABLE OF NUMBER;
    var_nt my_nested_table := my_nested_table(2,4,6,8,10,12,14,16,18,20);
BEGIN

    --Range Delete
    var_nt.DELETE;
    FOR i IN 1..var_nt.LAST LOOP
        IF var_nt.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE('Value at Index ['||i||'] is '|| var_nt(i));
        END IF;
    END LOOP;
END;
/

Calling collection procedure DELETE without any argument will delete all the elements of the collection over which it is applied. Likewise, in the above example we have a nested table with the name “my_nested_table” over which we applied the procedure DELETE. Thus, on successful execution, the procedure call will delete all the 10 numeric elements which are stored in it.

Suggested Reading: How to create nested table local to PL/SQL block.

Example 2: Procedure call with a single parameter

DECLARE
    TYPE my_nested_table IS
        TABLE OF NUMBER;
    var_nt my_nested_table := my_nested_table(2,4,6,8,10,12,14,16,18,20);
BEGIN
    DBMS_OUTPUT.PUT_LINE('After Deleted');
    --Delete Specific Index
    var_nt.DELETE(5);
    IF var_nt.EXISTS(5) THEN
            DBMS_OUTPUT.PUT_LINE('Value at Index [5] is '|| var_nt(5));
    ELSE
        DBMS_OUTPUT.PUT_LINE('Data is Deleted');
    END IF;
END;
/

This single parameter is the valid index number of the collection. Moreover, collection procedure DELETE called by passing a valid index number will remove the element of the specific index. So, in the above example we specified 5 as the argument to the PL/SQL collection method DELETE. Thus, on successful execution the procedure call will remove the element from the collection which is stored at index 5.

Example 3: Procedure call with two parameters.

DECLARE
    TYPE my_nested_table IS
        TABLE OF NUMBER;
    var_nt my_nested_table := my_nested_table(2,4,6,8,10,12,14,16,18,20);
BEGIN

    --Delete Range
    var_nt.DELETE(2,6);
    FOR i IN 1..var_nt.LAST LOOP
        IF var_nt.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE('Value at Index ['||i||'] is '|| var_nt(i));
        END IF;
    END LOOP;
END;
/

This way of calling DELETE procedure is termed as Range delete. Here you have to specify two Indexes and the procedure deletes the range of elements which fall between starting-index and ending-index. In the above example we set the starting index as 2 and ending index as 6. This means on execution the procedure call DELETE will remove all the elements which fall into this range.

Go ahead and copy the above codes into your SQL Developer and execute them yourself to see what the outputs are.

You can also watch the video tutorial on the same topic for live explanation of all the above examples.

That’s the tutorial on PL/SQL collection method DELETE in Oracle Database. Hope you enjoyed reading and learnt something new. Do make sure to share this blog on your social media. Also join me on my Facebook page for more interesting concepts of PL/SQL.

Thanks & have a great day!

1 COMMENT