Common challenges while using bulk collect and techniques to to deal with it

Bulk collects are nothing but select statements that retrieve multiple rows from single fetch into declared type known as collection.

FORALL is INSERTS,UPDATES,DELETES that uses collections to modify rows very quickly. The terms SQL Engine and PLSQL engine are used to reference mainly bulk collects and quite confusing. In simple way when I declare a variable using ‘ := ‘ assignment operator, secifying a block structure then PLSQL compilation and runtime system activates this is called PLSQL engine. PL/SQL statements are run by the PL/SQL statement executor while SQL statements are run by the SQL statement executor. When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. The SQL engine executes the SQL statement and returns information back to the PL/SQL engine . This transfer of control is called a context switch, and each one of these switches incurs overhead that slows down the overall performance of your programs.

General challenge we faced while using bulk dml is to trace if DML fails. Consider following example

bulkdml

If my insert fails here then how I can trace where it fails for that simple approach is

“Rewrite following cursor select and directly insert it into dept1 just for test purpose”. Here I purposely written ‘MKTG’ before dept_id column in cursor select statement to make wrong array sequence.

bulkdm

For large cursors selects this approach can be quick for trace during bulk collects as collection is single variable.

We can avoid ORA-24381 very quickly.

Author Profile

Tejas

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j