The keyword FORALL lets you run multiple DML statements very efficiently. It can only repeat a single DML statement, unlike a general-purpose FOR loop.
The SQL statement can reference more than one collection, but FORALL only improves performance where the index value is used as a subscript.
Usually, the bounds specify a range of consecutive index numbers. If the index numbers are not consecutive, such as after you delete collection elements, you can use the INDICES OF or VALUES OF clause to iterate over just those index values that really exist.
The INDICES OF clause iterates over all of the index values in the specified collection, or only those between a lower and upper bound.
The VALUES OF clause refers to a collection that is indexed by BINARY_INTEGER or PLS_INTEGER and whose elements are of type BINARY_INTEGER or PLS_INTEGER. The FORALL statement iterates over the index values specified by the elements of this collection.
Example 1 Issuing DELETE Statements in a Loop
This FORALL statement sends all three DELETE statements to the SQL engine at once:
CREATE TABLE employees2 AS SELECT * FROM employees;
DECLARE TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN FORALL i IN depts.FIRST..depts.LAST
DELETE FROM employees2 WHERE department_id = depts(i);
DROP TABLE employees2;
Example 2 Issuing INSERT Statements in a Loop
The following example loads some data into PL/SQL collections. Then it inserts the collection elements into a database table twice: first using a FOR loop, then using a FORALL statement. The FORALL version is much faster.
CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));
DECLARE TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
iterations CONSTANT PLS_INTEGER := 500;
FOR j IN 1..iterations LOOP -- load index-by tables pnums(j) := j; pnames(j) := 'Part No. ' || TO_CHAR(j); END LOOP; t1 := dbms_utility.get_time;
FOR i IN 1..iterations LOOP -- use FOR loop INSERT INTO parts1 VALUES (pnums(i), pnames(i)); END LOOP;
t2 := dbms_utility.get_time;
FORALL i IN 1..iterations -- use FORALL statement INSERT INTO parts2 VALUES (pnums(i), pnames(i));
t3 := dbms_utility.get_time; dbms_output.put_line('Execution Time (secs)');
dbms_output.put_line('FOR loop: ' || TO_CHAR((t2 - t1)/100));
dbms_output.put_line('FORALL: ' || TO_CHAR((t3 - t2)/100));
DROP TABLE parts1; DROP TABLE parts2;
Executing this block should show that the loop using FORALL is much faster.
Example 3 Using FORALL with Part of a Collection
The bounds of the FORALL loop can apply to part of a collection, not necessarily all the elements:
CREATE TABLE employees2 AS SELECT * FROM employees;
DECLARE TYPE NumList IS VARRAY(10) OF NUMBER;
depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);
BEGIN FORALL j IN 4..7 -- use only part of varray DELETE FROM employees2 WHERE department_id = depts(j);
DROP TABLE employees2;
Example 4 Using FORALL with Non-Consecutive Index Values
You might need to delete some elements from a collection before using the collection in a FORALL statement. The INDICES OF clause processes sparse collections by iterating through only the remaining elements.
You might also want to leave the original collection alone, but process only some elements, process the elements in a different order, or process some elements more than once. Instead of copying the entire elements into new collections, which might use up substantial amounts of memory, the VALUES OF clause lets you set up simple collections whose elements serve as "pointers" to elements in the original collection.
The following example creates a collection holding some arbitrary data, a set of table names. Deleting some of the elements makes it a sparse collection that would not work in a default FORALL statement. The program uses a FORALL statement with the INDICES OF clause to insert the data into a table. It then sets up two more collections, pointing to certain elements from the original collection. The program stores each set of names in a different database table using FORALL statements with the VALUES OF clause.
-- Create empty tables to hold order details CREATE TABLE valid_orders (cust_name VARCHAR2(32), amount NUMBER(10,2)); CREATE TABLE big_orders AS SELECT * FROM valid_orders WHERE 1 = 0; CREATE TABLE rejected_orders AS SELECT * FROM valid_orders WHERE 1 = 0; DECLARE -- Make collections to hold a set of customer names and order amounts. SUBTYPE cust_name IS valid_orders.cust_name%TYPE; TYPE cust_typ IS TABLe OF cust_name; cust_tab cust_typ; SUBTYPE order_amount IS valid_orders.amount%TYPE; TYPE amount_typ IS TABLE OF NUMBER; amount_tab amount_typ; -- Make other collections to point into the CUST_TAB collection. TYPE index_pointer_t IS TABLE OF PLS_INTEGER; big_order_tab index_pointer_t := index_pointer_t(); rejected_order_tab index_pointer_t := index_pointer_t(); PROCEDURE setup_data IS BEGIN -- Set up sample order data, including some invalid orders and some 'big' orders. cust_tab := cust_typ('Company 1','Company 2','Company 3','Company 4', 'Company 5'); amount_tab := amount_typ(5000.01, 0, 150.25, 4000.00, NULL); END; BEGIN setup_data(); dbms_output.put_line('--- Original order data ---'); FOR i IN 1..cust_tab.LAST LOOP dbms_output.put_line('Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i)); END LOOP; -- Delete invalid orders (where amount is null or 0). FOR i IN 1..cust_tab.LAST LOOP IF amount_tab(i) is null or amount_tab(i) = 0 THEN cust_tab.delete(i); amount_tab.delete(i); END IF; END LOOP; dbms_output.put_line('--- Data with invalid orders deleted ---'); FOR i IN 1..cust_tab.LAST LOOP IF cust_tab.EXISTS(i) THEN dbms_output.put_line('Customer #' || i || ', ' || cust_tab(i) || ': $' || amount_tab(i)); END IF; END LOOP; -- Since the subscripts of our collections are not consecutive, we use -- FORALL...INDICES OF to iterate through the actual subscripts, rather than 1..COUNT. FORALL i IN INDICES OF cust_tab INSERT INTO valid_orders(cust_name, amount) VALUES(cust_tab(i), amount_tab(i)); -- Now let's process the order data differently. We'll extract 2 subsets -- and store each subset in a different table. setup_data(); -- Initialize the CUST_TAB and AMOUNT_TAB collections again. FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP IF amount_tab(i) IS NULL OR amount_tab(i) = 0 THEN rejected_order_tab.EXTEND; -- Add a new element to this collection. rejected_order_tab(rejected_order_tab.LAST) := i; -- And record the subscript from the original collection. END IF; IF amount_tab(i) > 2000 THEN big_order_tab.EXTEND; -- Add a new element to this collection. big_order_tab(big_order_tab.LAST) := i; -- And record the subscript from the original collection. END IF; END LOOP; -- Now it's easy to run one DML statement on one subset of elements, and another DML statement on a different subset. FORALL i IN VALUES OF rejected_order_tab INSERT INTO rejected_orders VALUES (cust_tab(i), amount_tab(i)); FORALL i IN VALUES OF big_order_tab INSERT INTO big_orders VALUES (cust_tab(i), amount_tab(i)); COMMIT; END; / -- Verify that the correct order details were stored. SELECT cust_name "Customer", amount "Valid order amount" FROM valid_orders; SELECT cust_name "Customer", amount "Big order amount" FROM big_orders; SELECT cust_name "Customer", amount "Rejected order amount" FROM rejected_orders; DROP TABLE valid_orders; DROP TABLE big_orders; DROP TABLE rejected_orders;
How FORALL Affects Rollbacks
In a FORALL statement, if any execution of the SQL statement raises an unhandled exception, all database changes made during previous executions are rolled back. However, if a raised exception is caught and handled, changes are rolled back to an implicit savepoint marked before each execution of the SQL statement. Changes made during previous executions are not rolled back. For example, suppose you create a database table that stores department numbers and job titles, as follows. Then, you change the job titles so that they are longer. The second UPDATE fails because the new value is too long for the column. Because we handle the exception, the first UPDATE is not rolled back and we can commit that change.
CREATE TABLE emp2 (deptno NUMBER(2), job VARCHAR2(18)); DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(10, 20, 30); BEGIN INSERT INTO emp2 VALUES(10, 'Clerk'); INSERT INTO emp2 VALUES(20, 'Bookkeeper'); -- Lengthening this job title causes an exception. INSERT INTO emp2 VALUES(30, 'Analyst'); COMMIT; FORALL j IN depts.FIRST..depts.LAST -- Run 3 UPDATE statements. UPDATE emp2 SET job = job || ' (Senior)' WHERE deptno = depts(j); -- raises a "value too large" exception EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Problem in the FORALL statement.'); COMMIT; -- Commit results of successful updates. END; / DROP TABLE emp2;Counting Rows Affected by FORALL with the %BULK_ROWCOUNT Attribute
The cursor attributes SQL%FOUND, SQL%ISOPEN, SQL%NOTFOUND, and SQL%ROWCOUNT, return useful information about the most recently executed DML statement.
The SQL cursor has one composite attribute, %BULK_ROWCOUNT, for use with the FORALL statement. This attribute works like an associative array: SQL%BULK_ROWCOUNT(i) stores the number of rows processed by the ith execution of an INSERT, UPDATE or DELETE statement. For example:
CREATE TABLE emp2 AS SELECT * FROM employees; DECLARE TYPE NumList IS TABLE OF NUMBER; depts NumList := NumList(30, 50, 60); BEGIN FORALL j IN depts.FIRST..depts.LAST DELETE FROM emp2 WHERE department_id = depts(j); -- How many rows were affected by each DELETE statement? FOR i IN depts.FIRST..depts.LAST LOOP dbms_output.put_line('Iteration #' || i || ' deleted ' || SQL%BULK_ROWCOUNT(i) || ' rows.'); END LOOP; END; / DROP TABLE emp2;
The FORALL statement and %BULK_ROWCOUNT attribute use the same subscripts. For example, if FORALL uses the range 5..10, so does %BULK_ROWCOUNT. If the FORALL tatement uses the INDICES OF clause to process a sparse collection, %BULK_ROWCOUNT has corresponding sparse subscripts. If the FORALL statement uses the VALUES OF clause to process a subset of elements, %BULK_ROWCOUNT has subscripts corresponding to the values of the elements in the index collection. If the index collection contains duplicate elements, so that some DML statements are issued multiple times using the same subscript, then the corresponding elements of %BULK_ROWCOUNT represent the sum of all rows affected by the DML statement using that subscript. (For examples showing how to interpret %BULK_ROWCOUNT when using the INDICES OF and VALUES OF clauses, see the PL/SQL sample programs at http://otn.oracle.com/tech/pl_sql/.)
%BULK_ROWCOUNT is usually equal to 1 for inserts, because a typical insert operation affects only a single row. For the INSERT ... SELECT construct, %BULK_ROWCOUNT might be greater than 1. For example, the FORALL statement below inserts an arbitrary number of rows for each iteration. After each iteration, %BULK_ROWCOUNT returns the number of items inserted:
CREATE TABLE emp_by_dept AS SELECT employee_id, department_id FROM employees WHERE 1 = 0; DECLARE TYPE dept_tab IS TABLE OF departments.department_id%TYPE; deptnums dept_tab; BEGIN SELECT department_id BULK COLLECT INTO deptnums FROM departments; FORALL i IN 1..deptnums.COUNT INSERT INTO emp_by_dept SELECT employee_id, department_id FROM employees WHERE department_id = deptnums(i); FOR i IN 1..deptnums.COUNT LOOP -- Count how many rows were inserted for each department; that is, -- how many employees are in each department. dbms_output.put_line('Dept '||deptnums(i)||': inserted '|| SQL%BULK_ROWCOUNT(i)||' records'); END LOOP; dbms_output.put_line('Total records inserted =' || SQL%ROWCOUNT); END; / DROP TABLE emp_by_dept;
You can also use the scalar attributes %FOUND, %NOTFOUND, and %ROWCOUNT after running a FORALL statement. For example, %ROWCOUNT returns the total number of rows processed by all executions of the SQL statement.
%FOUND and %NOTFOUND refer only to the last execution of the SQL statement. You can use %BULK_ROWCOUNT to infer their values for individual executions. For example, when %BULK_ROWCOUNT(i) is zero, %FOUND and %NOTFOUND are FALSE and TRUE, respectively.
Handling FORALL Exceptions with the %BULK_EXCEPTIONS Attribute
PL/SQL provides a mechanism to handle exceptions raised during the execution of a FORALL statement. This mechanism enables a bulk-bind operation to save information about exceptions and continue processing.
To have a bulk bind complete despite errors, add the keywords SAVE EXCEPTIONS to your FORALL statement after the bounds, before the DML statement.
All exceptions raised during the execution are saved in the cursor attribute %BULK_EXCEPTIONS, which stores a collection of records. Each record has two fields:
%BULK_EXCEPTIONS(i).ERROR_INDEX holds the "iteration" of the FORALL statement during which the exception was raised.
%BULK_EXCEPTIONS(i).ERROR_CODE holds the corresponding Oracle error code.
The values stored by %BULK_EXCEPTIONS always refer to the most recently executed FORALL statement. The number of exceptions is saved in %BULK_EXCEPTIONS.COUNT. Its subscripts range from 1 to COUNT.
You might need to work backward to determine which collection element was used in the iteration that caused an exception. For example, if you use the INDICES OF clause to process a sparse collection, you must step through the elements one by one to find the one corresponding to %BULK_EXCEPTIONS(i).ERROR_INDEX. If you use the VALUES OF clause to process a subset of elements, you must find the element in the index collection whose subscript matches %BULK_EXCEPTIONS(i).ERROR_INDEX, and then use that element's value as the subscript to find the erroneous element in the original collection. (For examples showing how to find the erroneous elements when using the INDICES OF and VALUES OF clauses, see the PL/SQL sample programs at http://otn.oracle.com/tech/pl_sql/.)
If you omit the keywords SAVE EXCEPTIONS, execution of the FORALL statement stops when an exception is raised. In that case, SQL%BULK_EXCEPTIONS.COUNT returns 1, and SQL%BULK_EXCEPTIONS contains just one record. If no exception is raised during execution, SQL%BULK_EXCEPTIONS.COUNT returns 0.
Example 5 Bulk Operation That Continues Despite Exceptions
The following example shows how you can perform a number of DML operations, without stopping if some operations encounter errors:
CREATE TABLE emp2 AS SELECT * FROM employees; DECLARE TYPE NumList IS TABLE OF NUMBER; -- The zeros in this list will cause divide-by-zero errors. num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1); errors NUMBER; dml_errors EXCEPTION; PRAGMA exception_init(dml_errors, -24381); BEGIN -- SAVE EXCEPTIONS means don't stop if some DELETEs fail. FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS DELETE FROM emp2 WHERE salary > 500000/num_tab(i); -- If any errors occurred during the FORALL SAVE EXCEPTIONS, -- a single exception is raised when the statement completes. EXCEPTION WHEN dml_errors THEN -- Now we figure out what failed and why. errors := SQL%BULK_EXCEPTIONS.COUNT; dbms_output.put_line('Number of DELETE statements that failed: ' || errors); FOR i IN 1..errors LOOP dbms_output.put_line('Error #' || i || ' occurred during '|| 'iteration #' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX); dbms_output.put_line('Error message is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); END LOOP; END; / DROP TABLE emp2;
In this example, PL/SQL raised the predefined exception ZERO_DIVIDE when i equaled 2, 6, 10. After the FORALL statement, SQL%BULK_EXCEPTIONS.COUNT returned 3, and the contents of SQL%BULK_EXCEPTIONS were (2,1476), (6,1476), and (10,1476). To get the Oracle error message (which includes the code), we negated the value of SQL%BULK_EXCEPTIONS(i).ERROR_CODE and passed the result to the error-reporting function SQLERRM, which expects a negative number. Here is the output:
Number of errors is 3
Error 1 occurred during iteration 2
Oracle error is ORA-01476: divisor is equal to zero
Error 2 occurred during iteration 6
Oracle error is ORA-01476: divisor is equal to zero
Error 3 occurred during iteration 10
Oracle error is ORA-01476: divisor is equal to zero
PL/SQL sends SQL statements such as DML and queries to the SQL engine for execution, and SQL returns the result data to PL/SQL. You can minimize the performance overhead of this communication between PL/SQL and SQL by using the PL/SQL language features known collectively as bulk SQL. The FORALL statement sends INSERT, UPDATE, or DELETE statements in batches, rather than one at a time. The BULK COLLECT clause brings back batches of results from SQL. If the DML statement affects four or more database rows, the use of bulk SQL can improve performance considerably.
The assigning of values to PL/SQL variables in SQL statements is called binding. PL/SQL binding operations fall into three categories:
Bulk SQL uses PL/SQL collections, such as varrays or nested tables, to pass large amounts of data back and forth in a single operation. This process is known as bulk binding. If the collection has 20 elements, bulk binding lets you perform the equivalent of 20 SELECT, INSERT, UPDATE, or DELETE statements using a single operation. Queries can pass back any number of results, without requiring a FETCH statement for each row.
To speed up INSERT, UPDATE, and DELETE statements, enclose the SQL statement within a PL/SQL FORALL statement instead of a loop construct.
To speed up SELECT statements, include the BULK COLLECT INTO clause in the SELECT statement instead of using INTO.
The Profiler API is implemented as PL/SQL package DBMS_PROFILER, which provides services for gathering and saving run-time statistics. The information is stored in database tables, which you can query later. For example, you can learn how much time was spent executing each PL/SQL line and subprogram.
To use the Profiler, you start the profiling session, run your application long enough to get adequate code coverage, flush the collected data to the database, then stop the profiling session.
The Profiler traces the execution of your program, computing the time spent at each line and in each subprogram. You can use the collected data to improve performance. For instance, you might focus on subprograms that run slowly.
This package enables the collection of profiler (perfoprmance) data for performance improvement or for determining code coverage for PL/SQL applications. Application developers can use code coverage data to focus their incremental testing efforts.
With this interface, you can generate profiling information for all named library units that are executed in a session. The profiler gathers information at the PL/SQL virtual machine level. This information includes the total number of times each line has been executed, the total amount of time that has been spent executing that line, and the minimum and maximum times that have been spent on a particular execution of that line.
The profiling information is stored in database tables. This enables querying on the data: you can build customizable reports (summary reports, hottest lines, code coverage data, and so on. And you can analyze the data.The