bulk collect with select into statement in oracle database by manish sharma

Bulk Collect Clause With SELECT INTO Statement

Welcome to the second tutorial in PL/SQL Bulk Collect series where we will be learning how to use BULK COLLECT clause with SELECT-INTO statement.

Context switches are good but only when they are low in numbers. Increased number of context switches could cause problems such as poor query performance. This overhead caused by context switches could easily be controlled if we can compress them. This is exactly what you will learn in this tutorial.

You can learn more about SELECT-INTO here.

When should we use Bulk Collect with Select-Into statement?

When you are certain that the returning result of your SELECT statement is small then you should use Bulk Collect clause with Select-Into statement. Otherwise your bulk collect clause will make your Select-Into statement a memory hogging monster. Consequently it will slowdown the performance of your database. 

What should I do if my SELECT statement is retrieving huge number of rows?

You can always use LIMIT clause along with the Bulk Collect to limit the number of rows fetched from the database. But this is only possible when we are using the Bulk Collect clause with PL/SQL Cursors. Nonetheless, we will discuss about this in detail in the next tutorial.

Why can’t we limit the data fetched using Bulk Collect in SELECT-INTO statement?

When we use Bulk Collect clause with SELECT-INTO statement it uses implicit cursor to perform the task of bulk data processing. However Limit Clause can only be used with Bulk Collect clause, when the latter is using Explicit Cursor for data processing.

When does Bulk Collect Clause uses an Implicit Cursor and when an Explicit Cursor?

If we are using Bulk Collect Clause with a SQL statement such as SELECT-INTO it uses an implicit cursor. On the other hand whenever we use Bulk Collect clause with the FETCH statement it uses an explicit cursor.

I suggest you to read how to create an explicit cursor to learn more about FETCH statement.

Can we store the result returned by the bulk collect clause into a variable of primitive datatype?

No, as of now PL/SQL Collections are the only supportive datatypes for Bulk Data Processing with Bulk Collect Clause in Oracle Database. In case if you try to store the data retrieved using Bulk Collect clause into a variable of datatype such as Char, Number or Varchar2 you will get an error which will read something like this:

“PLS-00497: Cannot mix between single row and multi-row (BULK) in INTO list”

Definition of Bulk Collect Into Statement
“Bulk Collect Into” statement selects multiple data from a column and stores it into a SQL Collection.

Syntax of Bulk Collect clause with Select-Into statement.

The syntax for using PL/SQL Bulk Collect Clause with Select-Into statement in Oracle Database is as follow:

SELECT column_list
 BULK COLLECT INTO collection_datatype_name 
FROM table_name 
WHERE <where clause> 
ORDER BY <column list>;

Where:

Column List is the list of columns from which you want to retrieve the data.

Collection Datatype Name will be the name of the collection variable which will hold the data returned by the bulk collect clause.

Furthermore always remember every column you specified for retrieving the data must carry a corresponding collection datatype for holding that data. Because PL/SQL runtime engine always stores the data retrieved from the column into the collection in parallel manner.

For example

SELECT column_1, column_2 BULK COLLECT INTO collection_1, collection2 FROM table;

Here the data retrieved from column_1 will get stored into collection_1 and data from column_2 into collection_2 automatically.

Table Name could be the name of any table from which you want to retrieve the data.

Followed by that we have WHERE and ORDER BY clause which are optional but if you want you can use them.

Now that we have discussed the syntax, let’s do a simple example to learn how to work with PL/SQL Bulk Collect clause with SELECT INTO statement in Oracle Database.

SET SERVEROUTPUT ON;
DECLARE
    TYPE nt_fName   IS TABLE OF VARCHAR2 (20);
    TYPE nt_lName   IS TABLE OF VARCHAR2 (20);
    
    fname   nt_fName;
    lName   nt_lName;
BEGIN
    SELECT first_name, last_name 
        BULK COLLECT INTO fName, lName 
    FROM employees; 
        
        --Print values from the collection--
    FOR idx IN 1..fName.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE (idx||' - '||fName (idx) ||' '||lName (idx));
    END LOOP;
END;
/

Explanation Of The Example

Here we have a very simple code to demonstrate how to use Bulk Collect Clause with Select-Into statement. In the declaration section of this code we first created two Nested Table type collections “nt_fName” & “nt_lName” with their corresponding collection variables “fName” & “lName”. In fact both these collections will be used to hold the data returned by the Select-Into statement.

You can read how to create nested table here.

In the declaration section we have our Select-Into statement with Bulk Collect clause. Moreover by using this Select-Into statement we are retrieving all the data from First Name and Last Name column of the employees table. And with the help of Bulk Collect clause we are storing that data to corresponding collections in parallel manner.

Except along with the Select-Into statement we also have a “For Loop” which is printing the data of both the collections on to the output screen.

Must Watch
Do make sure to check out the video where I demonstrated how Bulk Collect Clause compresses multiple context switches into one and improves the performance of the query. Here is the link.

That is the tutorial explaining the concepts of Bulk Collect clause with Select-Into statement in detail. In this tutorial I have tried answering all the possible questions which you could face in your Certification exam as well as in the interview.

Hope you enjoyed reading. Please do make sure to SHARE this blog with your friends on your social. Thanks & have a great day!

1 COMMENT