BULK BINDING IN ORACLE

Oracle has two engines

1. PL/SQL engine
2. SQL engine

When ever an SQL statement appears in the PL/SQL block, it will be sent to SQL engine, known as
Context-switching.This involves overhead and processing.

For example, if we have a for loop executing insert statement 1000 times
(Inserting 1000 records)

for i in 1..1000
loop
insert into context_switch_tab (iteration_number) values (i);
end loop;

for every iteration, insert statement will be built and sent to the sql engine, meaning 1000 times
context switching occurs.

Here comes the concept of BULK BINDING.

Assume that you build the 1000 insert statements at a stretch and
pack them into a single bundle (TAR all the sql statements  or  zip all the sql statements)
and then send it to the sql engine to execute the 1000 insert statements.

In the above process, only one context-switch is needed for 1000 insert statements
(But make a note that memory is needed to  store the 1000 built sql insert statements till the context switch occurs)