Bulk collect in Oracle PL/SQL

Bulk collect:

   v Bulk collect is about reducing the context switching and improving the performance. Before going to bulk collect we need to know context switching.

Context switching:

   v Whenever we try to execute the PL/SQL program and execute it, The PL/SQL runtime engine starts preceding it line by line.

   v This engine processes all the PL/SQL statements by itself but it passes all the SQL statements which are coded into the PL/SQL block to the run time engine.

   v Those SQL statements will then get processed separately by the SQL engine, once it processing them the SQL engine then returns the results back to the PL/SQL engine, So that a combined result can be produced by the later, this to and hopping of control is called context switching.

   Disadvantage in context switching:

v The higher the hopping of control the greater will be overhead, which in turn will degrade the performance.

Bulk collect clause:

v Bulk collect clause reduces multiple control hopping by collecting all the SQL statement calls from PL/SQL program and sending them to SQL engine in just one go and vice versa.

Advantages of using Bulk collect clause:

v Bulk collect clause reduces or compresses multiple switches into a single context switch.

v It increases the performance and efficiency of a PL/SQL program.

Bulk data processing:

   v The process of fetching batches of data from PL/SQL runtime engine to SQL runtime engine and vice versa is called bulk data processing.

Bulk processing statement:

Bulk processing statement has two statements,

        1.    Bulk collect

        2.     FORALL

Bulk collect clause used with,

        1.    SELECT INTO

        2.    INSERT INTO

        3.    RETURNING - INTO


Bulk collect in SELECT INTO statement:


       Type e_ename is table of varchar2(50);

       empname e_ename;


       SELECT ename

       BULK COLLECT INTO empname

         FROM emp;


        for i in 1..empname.count


         DBMS_output.put_line(i || ' - ' || empname (i));

        End loop;




No comments:

Post a Comment

Popular Posts

Search This Blog

Powered by Blogger.

Blog Archive

Service Support

Need our help to Learn or Post New Concepts Contact me

Blog Archive

Recent Posts

Service Support

Need our help to Learn or Post New Concepts Contact me