» » » Here Bulk Binds (BULK COLLECT & FORALL) and Record Processing in OracleThis article is an update of one written for Oracle 8i which includes new featuresavailable in Oracle 9i Release 2 and beyond.Related articles.IntroductionOracle uses two engines to process PL/SQL code. All procedural code is handled by the PL/SQL engine while all SQL is handled bythe SQL statement executor, or SQL engine.There is an overhead associated with each context switch between the two engines. If PL/SQL code loops through acollection performing the same DML operation for each item in the collection it is possible to reduce context switches by bulkbinding the whole collection to the DML statement in one operation.In Oracle8i a collection must be defined for every column bound to the DML which can make the code rather long winded. Oracle9i allowsus to use Record structures during bulk operations so long as we don't reference individual columns of the collection. This restrictionmeans that updates and deletes which have to reference inividual columns of the collection in the where clause are still restrictedto the collection-per-column approach used in Oracle8i. BULK COLLECTBulk binds can improve the performance when loading collections from a queries. The BULK COLLECT INTO constructbinds the output of the query to the collection.
The select list must match the collections record definition exactly for this to be successful.Remember that collections are held in memory, so doing a bulk collect from a large query could cause a considerable performance problem. In actual fact you would rarely do a straight bulk collect in this manner. Instead you would limit the rows returned using the LIMIT clause and move through the data processing smaller chunks. This gives you the benefits of bulk binds, without hogging all the server memory.
Since no columns are specified in the insert statement the record structure of the collection must match the table exactly.Oracle9i Release 2 also allows updates using record definitions by using the ROW keyword. The following example uses the ROW keyword, when doing a comparison of normal and bulk updates.SET SERVEROUTPUT ONDECLARETYPE tidtab IS TABLE OF foralltest.id%TYPE;TYPE tforalltesttab IS TABLE OF foralltest%ROWTYPE;lidtab tidtab:= tidtab;ltab tforalltesttab:= tforalltesttab ;lstart NUMBER;lsize NUMBER:= 10000;BEGIN- Populate collections.FOR i IN 1. Lsize LOOPlidtab.extend;ltab.extend;lidtab(lidtab.last):= i;ltab(ltab.last).id:= i;ltab(ltab.last).code:= TOCHAR(i);ltab(ltab.last).description:= 'Description: ' TOCHAR(i);END LOOP;- Time regular updates.lstart:= DBMSUTILITY.gettime;FOR i IN ltab.first.
Ltab.last LOOPUPDATE foralltestSET ROW = ltab(i)WHERE id = ltab(i).id;END LOOP;DBMSOUTPUT.putline('Normal Updates: ' (DBMSUTILITY.gettime - lstart));lstart:= DBMSUTILITY.gettime;- Time bulk updates.FORALL i IN ltab.first. Ltab.lastUPDATE foralltestSET ROW = ltab(i)WHERE id = lidtab(i);DBMSOUTPUT.putline('Bulk Updates: ' (DBMSUTILITY.gettime - lstart));COMMIT;END;/Normal Updates: 235Bulk Updates: 20PL/SQL procedure successfully completed.SQLThe reference to the ID column within the WHERE clause of the first update would cause the bulk operation to fail, so the second update uses a separate collection for the ID column.
This restriction has been lifted in Oracle 11g, as documented.Once again, the output shows the performance improvements you can expect to see when using bulk binds. SQL%BULKROWCOUNTThe SQL%BULKROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement.
Every row in the driving collection has a corresponding row in the SQL%BULKROWCOUNT cursor attribute.The following code creates a test table as a copy of the ALLUSERS view. It then attempts to delete 5 rows from the table based on the contents of a collection. It then loops through the SQL%BULKROWCOUNT cursor attribute looking at the number of rows affected by each delete.CREATE TABLE bulkrowcounttest ASSELECT.FROM allusers;SET SERVEROUTPUT ONDECLARETYPE tarraytab IS TABLE OF VARCHAR2(30);larray tarraytab:= tarraytab('SCOTT', 'SYS','SYSTEM', 'DBSNMP', 'BANANA');BEGIN- Perform bulk delete operation.FORALL i IN larray.first. Larray.lastDELETE FROM bulkrowcounttestWHERE username = larray(i);- Report affected rows.FOR i IN larray.first. Larray.last LOOPDBMSOUTPUT.putline('Element: ' RPAD(larray(i), 15, ' ') ' Rows affected: ' SQL%BULKROWCOUNT(i));END LOOP;END;/Element: SCOTT Rows affected: 1Element: SYS Rows affected: 1Element: SYSTEM Rows affected: 1Element: DBSNMP Rows affected: 1Element: BANANA Rows affected: 0PL/SQL procedure successfully completed.SQLSo we can see that no rows were deleted when we performed a delete for the username 'BANANA'. SAVE EXCEPTIONS and SQL%BULKEXCEPTIONWe saw how the FORALL syntax allows us to perform bulk DML operations, but what happens if one of those individual operations results in an exception? If there is no exception handler, all the work done by the current bulk operation is rolled back.
How To Run Pl Sql Program In Mysql In Ubuntu
If there is an exception handler, the work done prior to the exception is kept, but no more processing is done. Neither of these situations is very satisfactory, so instead we should use the SAVE EXCEPTIONS clause to capture the exceptions and allow us to continue past them.
We can subsequently look at the exceptions by referencing the SQL%BULKEXCEPTION cursor attribute. To see this in action create the following table.CREATE TABLE exceptiontest (id NUMBER(10) NOT NULL);The following code creates a collection with 100 rows, but sets the value of rows 50 and 51 to NULL. Since the above table does not allow nulls, these rows will result in an exception. The SAVE EXCEPTIONS clause allows the bulk operation to continue past any exceptions, but if any exceptions were raised in the whole operation, it will jump to the exception handler once the operation is complete.
How To Save Pl Sql Program In Oracle 10g Pdf
In this case, the exception handler just loops through the SQL%BULKEXCEPTION cursor attribute to see what errors occured.SET SERVEROUTPUT ONDECLARETYPE ttab IS TABLE OF exceptiontest%ROWTYPE;ltab ttab:= ttab;lerrorcount NUMBER;exdmlerrors EXCEPTION;PRAGMA EXCEPTIONINIT(exdmlerrors, -24381);BEGIN- Fill the collection.FOR i IN 1. 100 LOOPltab.extend;ltab(ltab.last).id:= i;END LOOP;- Cause a failure.ltab(50).id:= NULL;ltab(51).id:= NULL;EXECUTE IMMEDIATE 'TRUNCATE TABLE exceptiontest';- Perform a bulk operation.BEGINFORALL i IN ltab.first. Ltab.last SAVE EXCEPTIONSINSERT INTO exceptiontestVALUES ltab(i);EXCEPTIONWHEN exdmlerrors THENlerrorcount:= SQL%BULKEXCEPTIONS.count;DBMSOUTPUT.putline('Number of failures: ' lerrorcount);FOR i IN 1. Lerrorcount LOOPDBMSOUTPUT.putline('Error: ' i ' Array Index: ' SQL%BULKEXCEPTIONS(i).errorindex ' Message: ' SQLERRM(-SQL%BULKEXCEPTIONS(i).ERRORCODE));END LOOP;END;END;/Number of failures: 2Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into PL/SQL procedure successfully completed.SQLAs expected the errors were trapped. If we query the table we can see that 98 rows were inserted correctly.SELECT COUNT(.)FROM exceptiontest;COUNT(.)-981 row selected.SQL Bulk Binds and TriggersFor bulk updates and deletes the timing points remain unchanged.
Each row in the collection triggers a before statement, before row, after row and after statement timing point. For bulk inserts, the statement level triggers only fire at the start and the end of the the whole bulk operation, rather than for each row of the collection.
This can cause some confusion if you are relying on the timing points from row-by-row processing.You can see an example of this. Updates. In Oracle 10g and above, the optimizing PL/SQL compiler rewrites conventional cursor for loops to use a BULK COLLECT with a LIMIT 100, so code that previously didn't take advantage of bulk binds may now run faster. Oracle 10g introduced support for handling sparse collections in FORALL statements.
The restriction on accessing individual columns of the collection with a FORALL has been removed in Oracle 11g.For more information see:.Hope this helps.