Hi,
This month is a continuation of the Bulk Binding PL/SQL technologies from Oracle from last month. Click here to review the Bulk Collect syntax in loading arrays very quickly with rows. This article will show the rest of the Bulk Binding technology, the FORALL statement that quickly puts rows back into the database. I will also show error/exception processing options that go with the FORALL statement.
Forall Bulk Binding
The FORALL syntax bulk binds the values in a collection to any bind variables (PL/SQL variables) present in the DML syntax.
FORALL supports all three collection types. However, FORALL is not particularly efficient with the Associative Array, as it has to always check for the presence of a value. Next month I’ll show you some PL/SQL updates that improve performance of the FORALL statement for Associative Arrays.
FORALL can save exceptions/errors by the element in the collection causing an issue.
FORALL supports the RETURNING bulk bind syntax.
SQL%ROWCOUNT contains the row count affected by a cursor SELECT or DML statement.
SQL%BULK_ROWCOUNT(<subscript>) contains the rows affected by each element passed to the DML statement. The subscript would be the same subscript pointing to the element in the collection.
There are two new terms to share:
- In-binding: takes what is in a collection and pushes it back to the Oracle database.
- Out-binding: is a return clause from a FORALL statement that bulk collects into a collection, showing useful information about what was processed by the DML associated with the FORALL statement.
In-binding is when a collection is used as input to a FORALL statement. Out-binding is when there is a RETURNING clause with a bulk collect syntax.
1:FOR i IN EMPNO_TABLE.FIRST .. EMPNO_TABLE.LAST
2: LOOP
3: update emp set sal = sal * 1.1 where empno =
4: EMPNO_TABLE(i);
5: END LOOP;
6: FORALL i in EMPNO_TABLE.FIRST .. EMPNO_TABLE.LAST
7: update emp set sal = sal * 1.1 where empno =
8: EMPNO_TABLE(i);
This syntax illustrates two coding styles. The FOR loop at lines 1 through 5 processes an UPDATE statement, one per element, in the collection EMPNO_TABLE. This coding style will cause a context switch between PL/SQL and SQL for each UPDATE statement. Context switches is a handoff between the PL/SQL engine and the Oracle kernel handling the SQL statement. These contact switches take a bit of time.
The FORALL clause (at lines 6 thru 8) performs the same task at a fraction of the time by processing all the contents of the collection in a single call to the database.
Looping and FORALL Syntax Execution and Times
This illustration shows the results from the code execution. Notice the UPDATE Emp via Loop versus the UPDATE EMP via FORALL. Even with the 14 rows in the EMP table, there is a considerable time difference.
FORALL Exception Processing
If the EXCEPTION clause were not included in the PL/SQL routine, the first row processed by a FORALL statement with a problem would cause an unhandled exception issue for the routine and all DML work would be rolled back.
FORALL also handles exceptions, allowing for the successful rows/elements to be processed and the elements that caused problems to be tracked.
There are 2 methods of processing FORALL exceptions:
- Using the keywords ‘SAVE EXCEPTIONS’
- saves exceptions in another collection
- uses original table subscript to locate rows with errors
- Using the keywords ‘LOG ERRORS’
- uses a pre-defined table to store error rows
- table can be modified to hold additional information
- easier to code, perhaps
- does not increase the size of the PL/SQL routine
The FORALL exceptions are tracked in a collection called SQL%BULK_EXCEPTIONS. The developer does not need to define this collection.
SQL%BULK_EXCEPTIONS.COUNT will contain the total number of exceptions encountered by the FORALL statement.
SQL%BULK_EXCEPTIONS(<subscript>).ERROR_INDEX will contain the Oracle error number. The subscript will match up with the element causing the exception in the collection passed to the FORALL statement.
SQL%BULK_EXCEPTIONS(<subscript>).ERROR_CODE will contain the Oracle error message.
When displaying elements from the collection that caused the exceptions, use SQL%BULK_ROWCOUNT(<subscript>). This variable contains the rows affected by the DML for each element in the table. If the rows affected are 0 then it is likely that this element caused an exception.
DECLARE
v_error_count NUMBER;
TYPE EMPNO_TYPE IS TABLE OF EMP.EMPNO%TYPE;
EMPNO_TABLE EMPNO_TYPE;
1: BEGIN
2: SELECT EMPNO
3: BULK COLLECT into EMPNO_Table
4: FROM EMP;
5: DBMS_OUTPUT.PUT_LINE('Nested Table Rows Loaded = ' || EMPNO_Table.COUNT );
6: FORALL i IN EMPNO_TABLE.FIRST .. EMPNO_TABLE.LAST
7: SAVE EXCEPTIONS
8: UPDATE EMP set SAL = SAL / 0 WHERE EMPNO =
9: EMPNO_TABLE(i);
10: EXCEPTION
11: WHEN OTHERS THEN
12: v_error_count := SQL%BULK_EXCEPTIONS.COUNT;
13: DBMS_OUTPUT.PUT_LINE('Errors Encountered = ' ||
14: v_error_count);
15: FOR i IN 1..v_error_count
16: LOOP
17: DBMS_OUTPUT.PUT_LINE('Empno: ' ||
EMPNO_TABLE(i) || ' ' ||
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || ':' ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
18: END LOOP;
19: DBMS_OUTPUT.PUT_LINE('EMPNO Not Updated: ');
20: FOR i IN 1..EMPNO_TABLE.COUNT
21: LOOP
22: IF SQL%BULK_ROWCOUNT(i) = 0 THEN
23: DBMS_OUTPUT.PUT(EMPNO_TABLE(i) || ' ');
24: END IF;
25: END LOOP;
26: DBMS_OUTPUT.NEW_LINE;
27: END;
This illustration shows the EXCEPTION processing for a FORALL with obvious errors (notice the divide by zero on line 8).
The keywords ‘SAVE EXCEPTIONS’ at line 10 is part of the FORALL syntax.
Each of the above-mentioned variables appears in this EXCEPTION clause. Notice the first LOOP that will process the collection SQL%BULK_EXCEPTIONS. Notice that the SQLERRM is negated on line 17. This is because Oracle error codes are negative numbers and this routine by default returns the Oracle error code in a positive number.
Notice the second LOOP (lines 21 thru 25) that prints out the affected elements that were originally passed to the FORALL statement.
FORALL Save Exceptions Exception Processing Execution
This illustration shows the output from the prior code example. EMP has 14 rows and since the divide by 0 was hard coded in the DML statement, each of the elements in the collection caused an exception in the DML.
LOG ERRORS is the other option for processing exceptions from a FORALL statement. This option puts the rows with exceptions into a ERR$_<table> for future processing. All rows without exceptions are committed to the database. The PL/SQL routine does not return an error condition.
The ERR$_<table> is created ahead of time. It contains the above-listed information. This same table should be processed after doing the FORALL statement, possibly using a separate routine. The rows should then be deleted so as to not to be confused with future executions of the update routine.
BEGIN
DBMS_ERRLOG.create_error_log (dml_table_name => 'EMP',
skip_unsupported => TRUE);
END;
Use this syntax to create the error log table for the EMP table.
Creating Error Log Table
Notice that all the EMP columns have a varchar2(4000) datatype. Notice the name of the table now has the base table along with a ERR$_ prefix.
FORALL i IN EMPNO_TABLE.FIRST .. EMPNO_TABLE.LAST
UPDATE EMP set SAL = SAL / 0 WHERE EMPNO = EMPNO_TABLE(i)
LOG ERRORS REJECT LIMIT UNLIMITED;
This syntax processes the EMP rows, again creating errors via a divide by zero.
Contents of ERR$_EMP Table.
The exceptions were indeed recorded in the ERR$_EMP table.
Some additional information to this log table might be useful. For example, if multiple programs are doing the FOR ALL … SAVE EXCEPTIONS, one might want to know who caused the error, when the error occurred, and which program logged the error.
It is OK to alter the ERR$ table and add columns. Next month I’ll illustrate how to collect some additional useful information, syntax you can maybe use in other exception processing.
The SAVE EXECPTIONS does record an error condition to the PL/SQL routine and the exception clause is then coded to process the array created holding the Oracle error codes.
This option is probably better if not expecting a lot of excepted rows.
The LOG ERRORS does cause DML. This log table will contain the Oracle error numbers and messages as well as the column data from the table being updated with the FORALL. This option does not make the PL/SQL routine larger (via another array) and this option does NOT return an error condition to the PL/SQL routine. The log table needs to be cleared prior to the FORALL statement and checked for exceptions after the FORALL statement.
Summary
FORALL can be a useful way of quickly putting rows in an array back to the database.
Next in the series will be some FORALL new syntax and live database timings using the FORALL statement from a customer. I will also illustrate some nifty exception-processing techniques that would be useful in the SAVE ERRORS processing along with your other exception processing, perhaps. I will also close on the FORALL discussion on best uses of this technology.
I hope you find these tips useful in your day to day use of the Oracle RDBMS.
Dan Hotka
Author/Instructor/Oracle Expert
Start the discussion at forums.toadworld.com