Dynamic SQL with bind varaibles

Oracle converts all the variables used in the PL/SQL into bind variables for performance optimization.
Bind variables improves the performance of a query singnificantly. It avoids the repetition of parsing.

srikar@ORCL> declare
  2  begin
  3  for a in (select empno from emp) loop
  4  delete from emp where empno=a.empno;
  5  end loop;
  6  
  7  end;
  8  
  9  
 10  /

PL/SQL procedure successfully completed.

Above, the variable (a.empno) used in the delete statement is converted to bind variable hence after running the above block, only one soft parse and 14 executions.
Below is the TKPROF output for the delete statement.

DELETE FROM EMP 
WHERE
 EMPNO=:B1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute     14      0.00       0.01          1         14         47          14
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      0.01       0.01          1         14         47          14

So then, what is the need of dynamic sql with binding enabled.
The theory says, Oracle goes for soft parse for each iteration where the iteration changes the variable value in the query in a *dynamic sql* unless you use 'USING' clasuse to bind the inputs.

The following blocks shows that a dynamic delete statement is used, we dint use the 'USING' clause to bind the variable, we simply used the varaible name. So, oracle goes 
for soft parse for each iteration, as there are 14 rows for the loop, 14 parses happens for the delete statement.


begin
   for a in (select empno from t_emp) loop
      execute immediate 'delete from t_emp where empno= '||a.empno;
   end loop;
end;
/

The TKPROF output,

delete from t_emp 
where
 empno= 7369


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          1          0           0
Execute      1      0.00       0.00          0          3          3           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          0          4          3           1


like the above plan (delete from t_emp where empno= 7369) there are 14 different delete statements for each employee number, each went for parse. Oracle didnt use single 
parse.

Then the below is the correct way, to bind the variables when used in dynamic sql

srikar@ORCL> declare
  2  begin
  4  for a in (select empno from t_emp) loop
  5  execute immediate 'delete from t_emp where empno= :a ' using a.empno;
  6  end loop;
  7  
  8  end;
  9  /

PL/SQL procedure successfully completed.

The below is the TKPROF output, it shows that the statement went for single parse and multiple
execution.

delete from t_emp 
where
 empno= :a 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     14      0.00       0.00          0         43         17          14
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       15      0.00       0.00          0         43         17          14