Bulk Collect Clause With FETCH INTO Statement

In the previous tutorial we managed to compress multiple context switches into a single one by using PL/SQL Bulk Collect with SELECT-INTO statement. SELECT-INTO statement is a SQL standard query which means developer does not have much control over the execution of the statement.

If we talk about query performance, we cannot go beyond an extent with SELECT-INTO statement. Let’s see how we can overcome all these shortcomings of SELECT-INTO statement.

In this blog you will learn how to use Bulk Collect clause with FETCH-INTO statement of an explicit cursor. As FETCH statements are part of cursor life cycle thus a working knowledge of explicit cursor is required for the better understanding of this tutorial. For the same you can check out the tutorial on how to create an explicit cursor in Oracle database.

Why should we use Bulk Collect clause with FETCH INTO statement?

As mentioned in the previous blog whenever we use Bulk-Collect with SELECT-INTO statement the runtime engine uses implicit cursor to process the task. But if we use Bulk Collect with FETCH-INTO statement then the runtime engine will use the explicit cursor to process the task.

An explicit cursor always helps us in getting advance control over our standard SQL queries. For example with an explicit cursor we can control when to fetch the records or how many records we want to retrieve at once however this is not possible in case of SELECT-INTO statement.

What is the syntax of Bulk Collect with Fetch-Into statement?

Similar to SELECT-INTO statement, bulk collect clause works as an attribute to the FETCH-INTO statement. Here is the syntax

FETCH <cursor_name> BULK COLLECT INTO <plsql_collection>;

FETCH statements are part of explicit cursor. If you try to execute them without declaring their parent cursor then you will get an error. Also always remember that PL/SQL collections are the only supported structure for bulk collect.

Example: How to use PL/SQL Bulk Collect with FETCH-INTO statement in Oracle Database.

SET SERVEROUTPUT ON;
DECLARE
--Create an explicit cursor
    CURSOR exp_cur IS
    SELECT first_name FROM employees;

    --Declare collection for holding the data 
    TYPE nt_fName   IS TABLE OF VARCHAR2 (20);
    fname   nt_fName;
BEGIN
    OPEN exp_cur;
    LOOP
        FETCH exp_cur BULK COLLECT INTO fname;
        EXIT WHEN fname.count=0;
        --Print data
        FOR idx IN fname.FIRST.. fname.LAST
        LOOP
            DBMS_OUTPUT.PUT_LINE (idx||' '||fname(idx) );
        END LOOP; 
    END LOOP;
    CLOSE exp_cur;
END;
/

I have explained every single line of this entire code in detail in the video tutorial on my YouTube channel. You can watch this tutorial.

Since in this code we have used Bulk Collect clause with the fetch into statement it means that context switching will not be a problem. As we know bulk collect clause compresses multiple switches into a single one so no further performance bottle neck.

But does it mean this code is well optimized & needs no further optimization? Or for that matter Query performance will never be an issue for us in the future?

Stay tuned, to get the answers to all these questions as well as to learn how we can further optimize this PL/SQL program.

Hope you enjoyed this blog. If you have any doubt or questions or you think I forgot to mention something in this blog then write to me on my Facebook page or Twitter. Thanks and have a great day!