PL/SQL Bulk Collect

Oracle Bulk Collect is a method of fetching data. With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection. The SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine.  Thus, when rows are retrieved using Oracle bulk collect, they are retrieved with only two context switches.  

When data involved is very large, we can use Bulk Collect clause to fetch the data into local PL/SQL variables faster without looping through one record at a time.  We can store the result set into either individual collection variables, if we are fetching certain number of columns or collection records, if we are fetching all the columns of the table.

Example:

SQL> declare
2 type emp_tab is table of emp%rowtype;
3 t_emp emp_tab;
4 begin
5 select * bulk collect into t_emp from emp;
6 dbms_output.put_line(t_emp.count);
7 end;
8 /

Posted in: Oracle Advanced Concepts,Oracle Notebook,Technical