bulk collect with execute immediate statement in oracle database by manish sharma

Bulk Collect With Execute Immediate Of Dynamic SQL

Wouldn’t it be amazing if we could minimize the performance bottleneck caused by context switches and optimize our dynamic SQL query. We already know that we can easily control the context switches through the use of bulk data processing.

If somehow we can combine the concepts of bulk data processing with dynamic SQL then we can easily reduce the bottleneck and enhance the performance of our query. That’s exactly what we will be learning in this tutorial.

In this tutorial we will learn how to properly use bulk collect with native dynamic SQL in Oracle Database. Moreover this tutorial will require a working knowledge of “Bulk Collect Into”. You can refer to PL/SQL tutorial 73 for that.

But before we start doing an example let’s first see the syntax of bulk collect with dynamic SQL.

Syntax of Bulk Collect with Dynamic SQL

 EXECUTE IMMEDIATE sql_qry BULK COLLECT INTO collection_variable;

Unlike traditional bulk collect into statement in dynamic SQL here it works more like an attribute of an ‘execute immediate’ statement. Do you want to know what will happen if we go with the traditional ‘Bulk Collect Into’ approach? You can watch my video tutorial here.

Anyways, let’s come back to the syntax. The statement starts with the reserved phrase “Execute Immediate”. Following that we have specified the variable into which we have stored our SQL query. And, right after that we have our “Bulk Collect Into” clause. It’s with the collection variable into which it will return and store the data.

Now let’s do the example.

Example of Bulk Collect Into with Execute Immediate.

SET SERVEROUTPUT ON;
DECLARE
    TYPE nt_Fname IS TABLE OF VARCHAR2 (60);
    fname nt_Fname;
    sql_qry VARCHAR2(150);
BEGIN
    sql_qry := 'SELECT first_name FROM employees';
    EXECUTE IMMEDIATE sql_qry BULK COLLECT INTO fname;
    FOR idx IN 1..fname.COUNT
        LOOP
            DBMS_OUTPUT.PUT_LINE(idx||' - '||fname(idx));
        END LOOP;
END;
/

Let’s understand this code section by section.

Declaration Section.

In the declaration section we have declared three things.

First we have declared a collection of nested table type nt_fname. We will be using this collection for storing the data returned by the dynamic SQL query. Second, we’ve declared a collection variable fname. The type of this collection variable is the nested table nt_fname. It will be used for referencing the nested table in our program.

Don’t know how collection ‘Nested Table’ works? Go ahead and check out the PL/SQL tutorial 51 here.

Third, we’ve declared a variable SQL_QRY for VARCHAR2 datatype. This variable will be used for storing the SQL query which will be executed with Execute Immediate of Dynamic SQL.

Using a variable for storing the SQL query for Execute Immediate is optional. It is more of like a personal choice. If you want you can write the SQL query directly in Execute Immediate statement. As opposed to storing it into a variable and using that variable with execute Immediate. I like the later approach of assigning the query into the variable. Because that gives a cleaner look to our code and makes it easier to debug.

Execution Section.

The first statement of the execution section is an assignment statement. Here we are storing a SQL query into the variable SQL_QEY using assignment operator (:=).

The query which we chose is a very simple SELECT statement. It’s returning all the data from the first name column of the employees table.  You can execute any supportive SQL statement.

The second statement is the dynamic SQL statement. Here we are executing our SQL query using Bulk Collect into with Execute Immediate. On execution, the Execute Immediate statement will execute the SQL query. And ‘Bulk Collect Into’ clause will store all the data returned by it into the collection nt_fname.

Third we have a ‘For loop’. This loop will display all the data which was processed by the Execute Immediate and stored into the collection nt_fname back to the user.

On successful execution you will see all the first names stored into employees table on your output screen.

That’s how we properly use a Bulk Collect Into clause with Execute Immediate of Dynamic SQL in Oracle Database. Hope you have learnt something new. If so then share this blog on your social media. Also, don’t forget to subscribe to our YouTube channel for more such interesting content.

Thanks and have a great day!

RELATED ARTICLESMORE FROM AUTHOR