Making the Most of this Tutorial

What You Should Know

Before taking this tutorial, you should be proficient with the basics of SQL and PL/SQL programming. For example, you know how to create procedures and functions. You know how to write DML statements. In addition, it'd be helpful to have some experience with collections, which are a requirement for use with bulk processing. You might find this blog post helpful: https://stevenfeuersteinonplsql.blogspot.com/2018/08/the-plsql-collection-resource-center.html

Be sure to run the Prerequisite SQL (red button above the list of modules) before trying to execute code in the modules. It creates a local copy of the employees table for you to work with.

If when you run code, you see this error...

ORA-00942: table or view does not exist

...that almost certainly means you skipped the setup step!

Run that same code again at any time if you'd like to reset the employees table to its "original" state.

Many of the blocks finish with a ROLLBACK statement to avoid changing the table's contents. Please don't conclude from these scripts that you should include a ROLLBACK in your exception handlers. That should be done only with great care and consideration.

You can run each of the code examples as is, to see the expected behavior. You can then modify that code as you like to play around with these fantastic features.

But you want to write some code yourself, right? That's how you learn best! So at the end of most of the modules, you will find:

• Fill in the Blanks: partially-written code that you need to finish up, that reinforces the content of that module.
• Exercises: You do all the coding to solve the stated requirement (be on the lookout for copy/paste opportunities from the module to speed things up).

The very last module of the tutorial ("Solutions to Exercises") contains my solutions to these exercises. I bet you could figure that out all by yourselves. :-)

VERY IMPORTANT: if you find any mistakes or have suggestions to improve this tutorial, you can submit feedback through LiveSQL or sent an email directly: steven dot feuerstein at oracle dot com (so there, bots! :-)).

The Row by Row Problem

Almost every program Oracle Database developers write includes both PL/SQL and SQL statements. PL/SQL statements are run by the PL/SQL statement executor; 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.

Let’s look at a concrete example to explore context switches more thoroughly and identify the reason that FORALL and BULK COLLECT can have such a dramatic impact on performance.

Suppose my manager asked me to write a procedure that accepts a department ID and a salary percentage increase and gives everyone in that department a raise by the specified percentage. Taking advantage of PL/SQL’s elegant cursor FOR loop and the ability to call SQL statements natively in PL/SQL, I can implement this requirement easily:

CREATE OR REPLACE PROCEDURE increase_salary (
   department_id_in   IN employees.department_id%TYPE,
   increase_pct_in    IN NUMBER)
IS
BEGIN
   FOR employee_rec
      IN (SELECT employee_id
            FROM employees
           WHERE department_id =
                    increase_salary.department_id_in)
   LOOP
      UPDATE employees emp
         SET emp.salary = emp.salary + 
             emp.salary * increase_salary.increase_pct_in
       WHERE emp.employee_id = employee_rec.employee_id;
      DBMS_OUTPUT.PUT_LINE ('Updated ' || SQL%ROWCOUNT);
   END LOOP;
END increase_salary;

Suppose there are 10000 employees in department 15. When I execute this block....

BEGIN
   increase_salary (50, .10);
   ROLLBACK; -- to leave the table in its original state
END;

....the PL/SQL engine will “switch” over to the SQL engine 10000 times, once for each row being updated. Tom Kyte of AskTom fame (https://asktom.oracle.com) refers to row-by-row switching like this as “slow-by-slow processing,” and it is definitely something to be avoided.

Possible Solutions

Generally, the way to improve performance over row-by-row context switching is to not perform row-by-row DML operations. This can be accomplished in one of two ways:

1. Implement the functionality in "pure" SQL - no PL/SQL loop.
2. Use the bulk processing features of PL/SQL.

If you can change your implementation to avoid a loop and instead simply execute a single DML statement, that should be done. For example, we can do this with the increase_salary procedure:

CREATE OR REPLACE PROCEDURE increase_salary (
   department_id_in   IN employees.department_id%TYPE,
   increase_pct_in    IN NUMBER)
IS
BEGIN
   UPDATE employees emp
      SET emp.salary =
               emp.salary
             + emp.salary * increase_salary.increase_pct_in
    WHERE emp.department_id = 
             increase_salary.department_id_in;
END increase_salary;

Of course, it is not always this easy. You might be doing some very complex processing of each row before doing the insert, update or delete that would be hard to do in SQL. You may need to do more nuanced error management than "all or nothing" SQL will allow. Or you might simply not have sufficient knowledge of SQL to do what's needed.

In an ideal world, you would stop programming and take an advanced SQL class (the Oracle Dev Gym offers a free one on SQL analytic functions). In the real world, you need to get the program up and running ASAP.

Whatever your situation, the bulk processing features of PL/SQL offer a straightforward solution - though there will be a lot to consider as you implement your conversion to BULK COLLECT and FORALL.

Let's first take a look at BULK COLLECT, which improves the performance of multi-row querying and is relatively simple. Then we'll move on to FORALL, which is used to execute the same non-query DML statement repeatedly, with different bind variables. That feature has a lot more moving parts and issues you need to take into account (which should come as no surprise, as you are changing data, not simply querying it.

Introduction to BULK COLLECT

To take advantage of bulk processing for queries, you simply put BULK COLLECT before the INTO keyword of your fetch operation, and then provide one or more collections after the INTO keyword.

Here are some things to know about how BULK COLLECT works:

• It can be used with all three types of collections: associative arrays, nested tables, and VARRAYs.
• You can fetch into individual collections (one for each expression in the SELECT list) or a single collection of records.
• The collection is always populated densely, starting from index value 1.
• If no rows are fetched, then the collection is emptied of all elements.

You can use BULK COLLECT in all these forms:

SELECT column(s) BULK COLLECT INTO collection(s)
FETCH cursor BULK COLLECT INTO collection(s)
EXECUTE IMMEDIATE query_string BULK COLLECT INTO collection(s)

Here's a block of code that fetches all rows in the employees table with a single context switch, and loads the data into a collection of records that are based on the table.

DECLARE
   TYPE employee_info_t IS TABLE OF employees%ROWTYPE;
   l_employees   employee_info_t;
BEGIN
   SELECT *
     BULK COLLECT INTO l_employees
     FROM employees
    WHERE department_id = 50;
   DBMS_OUTPUT.PUT_LINE (l_employees.COUNT);
END;

If you do not want to retrieve all the columns in a table, create your own user-defined record type and use that to define your collection. All you have to do is make sure the list of expressions in the SELECT match the record type's fields.

DECLARE
   TYPE two_cols_rt IS RECORD (
      employee_id   employees.employee_id%TYPE,
      salary        employees.salary%TYPE
   );

   TYPE employee_info_t IS TABLE OF two_cols_rt;

   l_employees   employee_info_t;
BEGIN
   SELECT employee_id, salary
     BULK COLLECT INTO l_employees
     FROM employees
    WHERE department_id = 50;
   DBMS_OUTPUT.PUT_LINE (l_employees.COUNT);
END;

Quick Tip

You can avoid the nuisance of declaring a record type to serve as the type for the collection through the use of a "template cursor." This cursor should have the same select list as the BULK COLLECT query. You can, however, leave off the WHERE clause and anything else after the FROM clause, because it will never be used for anything but a %ROWTYPE declaration. Here's an example:

DECLARE
   CURSOR employee_info_c IS
      SELECT employee_id, salary 
        FROM employees ;

   TYPE employee_info_t IS TABLE OF employee_info_c%ROWTYPE;

   l_employees   employee_info_t;
BEGIN
   SELECT employee_id, salary
     BULK COLLECT INTO l_employees
     FROM employees
    WHERE department_id = 10;
END;

Fill in the Blanks

In the block below replace the #FINISH# tags with code so that the last names of all employees in department 50 are displayed.

DECLARE
   #FINISH#
   l_names   names_t;
BEGIN
   SELECT last_name
     #FINISH#
     FROM employees
    WHERE department_id = 50
    ORDER BY last_name;
END;

Exercise 1

Write a stored procedure that accepts a department ID, uses BULK COLLECT to retrieve all employees in that department, and displays their first name and salary. Then write an anonymous block to run that procedure for department 100.

Managing PGA Memory with the LIMIT Clause

As with almost all other types of variables and constants you use in your code, collections consume PGA (process global area) memory. If your collection gets too large, your users might encounter an error. To see this happen, run the code below (note: varchar2a is a collection type of strings defined in the DBMS_SQL package).

DECLARE
   l_strings   DBMS_SQL.varchar2a;
BEGIN
   FOR indx IN 1 .. 2 ** 31 - 1
   LOOP
      l_strings (indx) := RPAD ('abc', 32767, 'def');
   END LOOP;
END;

When using BULK COLLECT, you could attempt to retrieve too many rows in one context switch and run out of PGA memory. To help you avoid such errors, Oracle Database offers a LIMIT clause for BULK COLLECT. Indeed, when using BULK COLLECT we recommend that you never or at least rarely use an "unlimited" BULK COLLECT which is what you get with a SELECT BULK COLLECT INTO (an implicit query) - and you saw in the previous module.

Instead, declare a cursor (or a cursor variable), open that cursor, and then in a loop, retrieve N number of rows with each fetch.

In the block below, I set my fetch limit to just 10 rows to demonstrate how this feature works. You will likely never want to set the limit to less than 100 - this topic is explored further below.

DECLARE
   c_limit PLS_INTEGER := 10;

   CURSOR employees_cur
   IS
      SELECT employee_id
        FROM employees
       WHERE department_id = 50;

   TYPE employee_ids_t IS TABLE OF 
      employees.employee_id%TYPE;

   l_employee_ids   employee_ids_t;
BEGIN
   OPEN employees_cur;

   LOOP
      FETCH employees_cur
      BULK COLLECT INTO l_employee_ids
      LIMIT c_limit;

      DBMS_OUTPUT.PUT_LINE (l_employee_ids.COUNT || ' fetched');

      EXIT WHEN l_employee_ids.COUNT = 0;
   END LOOP;
END;

One thing to watch out for when switching to LIMIT with BULK COLLECT (in a loop) is following the same pattern for single-row fetching in a loop. I demonstrate this issue below, but first, a reminder: there are 107 rows in the employees table.

SELECT COUNT(*) FROM employees

Here's the common way to terminate a loop in which you fetch row-by-row from an explicit cursor:

DECLARE
   CURSOR emps_c IS SELECT * FROM employees;
   l_emp   emps_c%ROWTYPE;
   l_count INTEGER := 0;
BEGIN
   OPEN emps_c;

   LOOP
      FETCH emps_c INTO l_emp;
      EXIT WHEN emps_c%NOTFOUND;
      DBMS_OUTPUT.put_line (l_emp.employee_id);
      l_count := l_count + 1;
   END LOOP;
   DBMS_OUTPUT.put_line ('Total rows fetched: ' || l_count);
END;

In other words: fetch a row, stop if the cursor has retrieved all rows. Now let's switch to using BULK COLLECT and LIMIT, fetching 10 rows at a time, using the same approach to exiting the loop.

DECLARE
   CURSOR emps_c IS SELECT * FROM employees;
   TYPE emps_t IS TABLE OF emps_c%ROwTYPE;
   l_emps   emps_t;
   l_count INTEGER := 0;
BEGIN
   OPEN emps_c;

   LOOP
      FETCH emps_c BULK COLLECT INTO l_emps LIMIT 10;
      EXIT WHEN emps_c%NOTFOUND;
      DBMS_OUTPUT.put_line (l_emps.COUNT);
      l_count := l_count + l_emps.COUNT;
   END LOOP;
   DBMS_OUTPUT.put_line ('Total rows fetched: ' || l_count);
END;

Wait, what? Is that right? Do I see "Total rows fetched: 100"? Yes, I do. And therein lies the trap. You cannot continue to use the same EXIT WHEN statement in the same place in your loop when you switch to BULK COLLECT with LIMIT.

The very last fetch performed retrieved the last 7 rows, but also exhausted the cursor. So the %NOTFOUND returns TRUE, while the collection has those 7 elements in it.

To terminate a loop using BULK COLLECT with LIMIT, you should either:

• Move the EXIT WHEN to the bottom of the loop body, or
• Ignore the cursor and check the collection. When empty, terminate.

These two approaches are shown below.

DECLARE
   CURSOR emps_c IS SELECT * FROM employees;
   TYPE emps_t IS TABLE OF emps_c%ROwTYPE;
   l_emps   emps_t;
   l_count INTEGER := 0;
BEGIN
   OPEN emps_c;
   LOOP
      FETCH emps_c BULK COLLECT INTO l_emps LIMIT 10;
      l_count := l_count + l_emps.COUNT;
      EXIT WHEN emps_c%NOTFOUND;
   END LOOP;
   DBMS_OUTPUT.put_line ('Total rows fetched: ' || l_count);
END;

DECLARE
   CURSOR emps_c IS SELECT * FROM employees;
   TYPE emps_t IS TABLE OF emps_c%ROwTYPE;
   l_emps   emps_t;
   l_count INTEGER := 0;
BEGIN
   OPEN emps_c;

   LOOP
      FETCH emps_c BULK COLLECT INTO l_emps LIMIT 10;
      EXIT WHEN l_emps.COUNT = 0;
      l_count := l_count + l_emps.COUNT;
   END LOOP;
   DBMS_OUTPUT.put_line ('Total rows fetched: ' || l_count);

   CLOSE emps_c;
END;

Fill in the Blanks

The employees table has 107 rows in it. In the block below replace the #FINISH# tags with code so that when the block is executed, the following text is displayed:

Rows fetched 25
Rows fetched 25
Rows fetched 25
Rows fetched 25
Rows fetched 7
Rows fetched 0

DECLARE
   CURSOR ids_c IS SELECT employee_id FROM employees;
   #FINISH#
   l_count INTEGER;
BEGIN
   OPEN ids_c;

   LOOP
      FETCH ids_c #FINISH#;
      DBMS_OUTPUT.put_line ('Rows fetched: ' || l_count);
      EXIT WHEN #FINISH#;
   END LOOP;

   CLOSE ids_c;
END;

Exercise 2

Write an anonymous block that fetches (using BULK COLLECT) only the last name and salary from the employees table 5 rows at a time, and then displays that information. Make sure 107 names and salaries are shown!

Cursor FOR Loops and BULK COLLECT

When should you convert a non-bulk query to one using BULK COLLECT? More specifically, should you convert a cursor FOR loop to an explicit cursor and FETCH BULK COLLECT with limit? Here are some things to keep in mind:

• As long as your PL/SQL optimization level is set to 2 (the default) or higher, the compiler will automatically optimize cursor FOR loops to retrieve 100 rows with each fetch. You cannot modify this number.
• If your cursor FOR loop is "read only" (it does not execute non-query DML), then you can probably leave it as is. That is, fetching 100 rows with each fetch will usually give you sufficient improvements in performance over row-by-row fetching.
• Only cursor FOR loops are optimized this way, so if you have a simple or WHILE loop that fetches individual rows, you should convert to BULK COLLECT - with LIMIT!
• If you are fetching a very large number of rows, such as might happen with data warehouse processing or a nightly batch process, then you should experiment with larger LIMIT values to see what kind of "bang for the buck" you will get.
• If your cursor FOR loop (or any other kind of loop for that matter) contains one or more non-query DML statements (insert, update, delete, merge), you should convert to BULK COLLECT and FORALL.

Run the following code to see how optimization affects cursor FOR loop performance.

CREATE OR REPLACE PROCEDURE test_cursor_performance (approach IN VARCHAR2)
IS
   CURSOR cur IS
      SELECT * FROM all_source WHERE ROWNUM < 100001;

   one_row cur%ROWTYPE;

   TYPE t IS TABLE OF cur%ROWTYPE INDEX BY PLS_INTEGER;

   many_rows     t;
   last_timing   NUMBER;
   cntr number := 0;

   PROCEDURE start_timer
   IS
   BEGIN
      last_timing := DBMS_UTILITY.get_cpu_time;
   END;

   PROCEDURE show_elapsed_time (message_in IN VARCHAR2 := NULL)
   IS
   BEGIN
      DBMS_OUTPUT.put_line (
            '"'
         || message_in
         || '" completed in: '
         || TO_CHAR (
               ROUND ( (DBMS_UTILITY.get_cpu_time - last_timing) / 100, 2)));
   END;
BEGIN
   start_timer;

   CASE approach
      WHEN 'implicit cursor for loop'
      THEN
         FOR j IN cur
         LOOP
            cntr := cntr + 1;
         END LOOP;

         DBMS_OUTPUT.put_line (cntr);

      WHEN 'explicit open, fetch, close'
      THEN
         OPEN cur;

         LOOP
            FETCH cur INTO one_row;
            EXIT WHEN cur%NOTFOUND;
            cntr := cntr + 1;
         END LOOP;

         DBMS_OUTPUT.put_line (cntr);

         CLOSE cur;
      WHEN 'bulk fetch'
      THEN
         OPEN cur;

         LOOP
            FETCH cur BULK COLLECT INTO many_rows LIMIT 100;
            EXIT WHEN many_rows.COUNT () = 0;

            FOR indx IN 1 .. many_rows.COUNT
            loop
               cntr := cntr + 1;
            end loop;
         END LOOP;

         DBMS_OUTPUT.put_line (cntr);

         CLOSE cur;
   END CASE;

   show_elapsed_time (approach);
END test_cursor_performance;
/

/* Try different approaches with optimization disabled. */

ALTER PROCEDURE test_cursor_performance
COMPILE plsql_optimize_level=0
/

BEGIN
   dbms_output.put_line ('No optimization...');
   
   test_cursor_performance ('implicit cursor for loop');

   test_cursor_performance ('explicit open, fetch, close');

   test_cursor_performance ('bulk fetch');
END;
/

/* Try different approaches with default optimization. */

ALTER PROCEDURE test_cursor_performance
COMPILE plsql_optimize_level=2
/

BEGIN
   DBMS_OUTPUT.put_line ('Default optimization...');

   test_cursor_performance ('implicit cursor for loop');

   test_cursor_performance ('explicit open, fetch, close');

   test_cursor_performance ('bulk fetch');
END;
/

Exercise 3

This exercise has two parts (and for this exercise assume that the employees table has 1M rows with data distributed equally amongst departments: (1) Write an anonymous block that contains a cursor FOR loop that does not need to be converted to using BULK COLLECT. (2) Write an anonymous block that contains a cursor FOR loop that does need to use BULK COLLECT (assume it cannot be rewritten in "pure" SQL).

RETURNING and BULK COLLECT

The RETURNING clause is a wonderful thing. If you are inserting, updating or deleting data, and you need to get some information back after the statement completes (such as the primary key of the newly-inserted row), RETURNING is the thing for you! Here's an example:

CREATE TABLE t (
   id NUMBER GENERATED ALWAYS AS IDENTITY,
   n NUMBER)
/

DECLARE
   l_id t.id%TYPE;
BEGIN
   INSERT INTO t (n) VALUES (100)
      RETURNING id INTO l_id;
   DBMS_OUTPUT.PUT_LINE (l_id);
END;
/

Suppose, however, that I am changing more than one row. Can I use RETURNING then? Let's see....

DECLARE
   l_id employees.employee_id%TYPE;
BEGIN
   UPDATE employees
      SET last_name = UPPER (last_name)
      RETURNING employee_id INTO l_id;
   ROLLBACK;
END;
/

Oh no!

ORA-01422: exact fetch returns more than requested number of rows

But wait, that's the sort of error you can with a SELECT-INTO that returns more than one row. Why is it showing up here?

Because the RETURNING clause is essentially translated into a SELECT-INTO: get one value and stuff it into l_id. But in this case, the UPDATE statement is returning many IDs. How do we get this work?

BULK COLLECT to the rescue! I need to take multiple values and put them into something. What could that be? How about a collection? So, yes, if you are changing one or more rows, change INTO to BULK COLLECT INTO, and provide a collection to hold the values.

DECLARE
   TYPE ids_t IS TABLE OF employees.employee_id%TYPE;
   l_ids ids_t;
BEGIN
   UPDATE employees
      SET last_name = UPPER (last_name)
    WHERE department_id = 50
      RETURNING employee_id BULK COLLECT INTO l_ids;

   FOR indx IN 1 .. l_ids.COUNT 
   LOOP
      DBMS_OUTPUT.PUT_LINE (l_ids (indx));
   END LOOP;

   ROLLBACK;
END;
/

Now RETURNING works like a charm. Thanks, BULK COLLECT!

Fill in the Blanks

In the block below replace the #FINISH# tag with code so that "Deleted = 3" is displayed after execution.

DECLARE
   TYPE ids_t IS TABLE OF employees.employee_id%TYPE;
   l_ids ids_t;
BEGIN
   DELETE FROM employees
    WHERE salary > 15000
    #FINISH#

   DBMS_OUTPUT.PUT_LINE ('Deleted = ' || l_ids.COUNT);

   ROLLBACK;
END;
/

Exercise 4:

Write an anonymous block that deletes all the rows in the employees table for department 50 and returns all the employee IDs and the last names of deleted rows. Then display those values using DBMS_OUTPUT.PUT_LINE. Finally, you might want to rollback. That will make it easier to test your code - and continue on with the tutorial.

Dynamic SQL and BULK COLLECT

You can also use BULK COLLECT with native dynamic SQL queries that might return more than one row. As with SELECT-INTO and FETCH-INTO, just stick that "BULK COLLECT" before the INTO and provide a collection (or multiple) to hold the multiple values returned.

DECLARE
   TYPE ids_t IS TABLE OF employees.employee_id%TYPE;

   l_ids   ids_t;
BEGIN
   EXECUTE IMMEDIATE
      'SELECT employee_id FROM employees WHERE department_id = :dept_id'
      BULK COLLECT INTO l_ids
      USING 50;

   FOR indx IN 1 .. l_ids.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_ids (indx));
   END LOOP;
END;

You can even get "fancy" and use BULK COLLECT in the RETURNING clause of a dynamic DML statement:

DECLARE
   TYPE ids_t IS TABLE OF employees.employee_id%TYPE;
   l_ids ids_t;
BEGIN
   EXECUTE IMMEDIATE
      'UPDATE employees SET last_name = UPPER (last_name)
          WHERE department_id = 100
          RETURNING employee_id INTO :ids'
   RETURNING BULK COLLECT INTO l_ids;
   
   FOR indx IN 1 .. l_ids.COUNT 
   LOOP
      DBMS_OUTPUT.PUT_LINE (l_ids (indx));
   END LOOP;
END;

Exercise 5:

Write the rest of the procedure whose signature is shown below. Use BULK COLLECT to fetch all the last names from employees identified by that WHERE clause and return the collection. Then write an anonymous block to test your procedure: pass different WHERE clauses, and display names retrieved.

PROCEDURE get_names (
   where_in IN VARCHAR2,
   names_out OUT DBMS_SQL.VARCHAR2_TABLE)

Introduction to FORALL

Whenever you execute a non-query DML statement inside of a loop, you should convert that code to use FORALL - and that's if you cannot get rid of the loop entirely and handle the requirement with "pure SQL."

The FORALL statement is not a loop; it is a declarative statement to the PL/SQL engine: “Generate all the DML statements that would have been executed one row at a time, and send them all across to the SQL engine with one context switch.”

We will start with a very simple example, showing the basic conversion from a loop to FORALL. Then we will explore the many nuances of working with this powerful feature.

In the block below, I have a classic example of the row-by-row "anti-pattern:" delete each employee in department 50 and 100.

BEGIN
   FOR emp_rec IN (SELECT employee_id
                     FROM employees
                    WHERE department_id IN (50, 100))
   LOOP
      DELETE FROM employees
            WHERE employee_id = emp_rec.employee_id;
   END LOOP;

   ROLLBACK;
END;

Now, first of all I am sure you have noticed that this entire loop could be replaced by a single UPDATE statement, as in:

DELETE FROM employees
 WHERE department_id IN (50, 100)

For the purposes of demonstration (and for the remainder of this tutorial), please assume that there is more going on that justifies the use of FORALL. In which case, here is the rewrite of the original block:

DECLARE
   TYPE ids_t IS TABLE OF employees.department_id%TYPE;
   l_ids ids_t := ids_t (50, 100);
BEGIN
   FORALL l_index IN 1 .. l_ids.COUNT
      DELETE FROM employees
            WHERE department_id = l_ids (l_index);

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
   ROLLBACK;
END;

Here's what I modified to convert to bulk processing:

1. Declare a nested table type of department IDs, and declare a variable based on that type. Initialize the nested table with two departments.
2. Replace the FOR loop with a FORALL header. In this simplest of all cases, it looks just like a numeric FOR loop header, iterating from 1 to the number of elements in the nested table. Note the implicitly declared l_index iterator.
3. Place the DELETE statement "under" the FORALL; replace the IN clause with an equality check and reference an element in the l_ids bind array using the l_index iterator; and terminate the whole statement with a semi-colon.
4. Display the number of rows modified by the entire FORALL statement, and then rollback changes so that the table is left intact for the next example.

That's about as simple an example of FORALL as one could imagine. The real world is usually a little bit more interesting. For example, you will often have an array of values that you bind into the WHERE clause and also one or more arrays of values that you use in the SET clause of an update statement (or to provide values for columns of an INSERT statement in the VALUES clause).

FORALL takes care of all that exactly as you'd expect - but it is up to you to make sure that the data is in synch across all your collections. In the block below, I update rows by employee ID with a new salary and first name specific to each ID.

DECLARE
   /* I use pre-defined collection types to reduce code volume. */
   l_ids        DBMS_SQL.number_table;
   l_names      DBMS_SQL.varchar2a;
   l_salaries   DBMS_SQL.number_table;
BEGIN
   l_ids (1) := 101;
   l_ids (2) := 112;
   l_ids (3) := 120;
   
   l_names (1) := 'Sneezy';
   l_names (2) := 'Bashful';
   l_names (3) := 'Happy';
   
   l_salaries (1) := 1000;
   l_salaries (2) := 1500;
   l_salaries (3) := 2000;

   FORALL indx IN 1 .. l_ids.COUNT
      UPDATE employees
         SET first_name = l_names (indx), salary = l_salaries (indx)
       WHERE employee_id = l_ids (indx);

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);

   FOR emp_rec IN (  SELECT first_name, salary
                       FROM employees
                      WHERE employee_id IN (101, 112, 120)
                   ORDER BY employee_id)
   LOOP
      DBMS_OUTPUT.put_line (emp_rec.first_name || ' - ' || emp_rec.salary);
   END LOOP;

   ROLLBACK;
END;

If I do not keep the indexes in synch across all columns, I will get errors, as you will see when you run this block:

DECLARE
   l_ids        DBMS_SQL.number_table;
   l_names      DBMS_SQL.varchar2a;
   l_salaries   DBMS_SQL.number_table;
BEGIN
   l_ids (1) := 101;
   l_ids (2) := 112;
   l_ids (3) := 120;
   
   l_names (1) := 'Sneezy';
   l_names (100) := 'Happy';
   
   l_salaries (-1) := 1000;
   l_salaries (200) := 1500;
   l_salaries (3) := 2000;

   FORALL indx IN 1 .. l_ids.COUNT
      UPDATE employees
         SET first_name = l_names (indx), salary = l_salaries (indx)
       WHERE employee_id = l_ids (indx);
   
   ROLLBACK;
END;

ORA-22160: element at index [N] does not exist

So: by all means, you can reference multiple bind arrays, but make sure they are consistent across index values (often this is not an issue, as the collections are loaded from the same BULK COLLECT query).

Here are some important things to remember about FORALL:

• Each FORALL statement may contain just a single DML statement. If your loop contains two updates and a delete, then you will need to write three FORALL statements.
• PL/SQL declares the FORALL iterator as an integer, just as it does with a FOR loop. You do not need to (and should not) declare a variable with this same name.
• In at least one place in the DML statement, you need to reference a collection and use the FORALL iterator as the index value in that collection.
• When using the IN low_value . . . high_value syntax in the FORALL header, the collections referenced inside the FORALL statement must be densely filled. That is, every index value between the low_value and high_value must be defined.
• If your collection is not densely filled, you should use the INDICES OF or VALUES OF syntax in your FORALL header.

Fill in the Blanks

In the block below replace the #FINISH# tags with code so that the last names of employees with IDs 111, 121 and 131 are all upper-cased.

DECLARE
   l_ids        DBMS_SQL.number_table;
   l_names      DBMS_SQL.varchar2a;
BEGIN
   #FINISH#

   FORALL indx IN 0 .. #FINISH#
      UPDATE employees
         SET first_name = UPPER (l_names (indx))
       WHERE employee_id = l_ids (indx);
   
   ROLLBACK;
END;

Exercise 6:

Write an anonymous block that uses BULK COLLECT to populate two collections, one with the employee Id and the other with the employee's last name. Use those collections in a FORALL statement to update the employee's last name to the UPPER of the name in the name collection, for each employee ID. Note: clearly, you do not need BULK COLLECT and FORALL to do this. Please pretend. :-)

SQL%ROWCOUNT and SQL%BULK_ROWCOUNT

SQL%ROWCOUNT returns the total number of rows modified by the FORALL, overall. SQL%BULK_ROWCOUNT is a pseudo-collection** that contains one element for each DML statement executed by FORALL. The element contains the number of rows modified by that specific DML statement.

In the code below, I create a procedure to update salaries for specified name filters. Then I show the total number of rows modified. After that, I iterate through the pseudo-collection to show total number of rows modified. In addition, I apply a quality assurance check: the rule for this code is that if any filter does not change any rows, then I raise an exception. This is just one way that you might want to use SQL%BULK_ROWCOUNT.

CREATE OR REPLACE TYPE filter_nt IS TABLE OF VARCHAR2(100)
/

CREATE OR REPLACE PROCEDURE update_by_filter (filter_in IN filter_nt)
IS
BEGIN
   FORALL indx IN 1 .. filter_in.COUNT
      UPDATE employees
         SET salary = salary * 1.1
       WHERE UPPER (last_name) LIKE filter_in (indx);

   DBMS_OUTPUT.put_line ('Total rows modified = ' || SQL%ROWCOUNT);

   FOR indx IN 1 .. filter_in.COUNT
   LOOP
      IF SQL%BULK_ROWCOUNT (indx) = 0
      THEN
         raise_application_error (
            -20000,
            'No rows found for filter "' || filter_in (indx) || '"');
      ELSE
         DBMS_OUTPUT.put_line (
               'Number of employees with names like "'
            || filter_in (indx)
            || '" given a raise: '
            || SQL%BULK_ROWCOUNT (indx));
      END IF;
   END LOOP;

   ROLLBACK;
END;
/

BEGIN
   update_by_filter (filter_nt ('S%', 'E%', '%A%'));
END;
/

And now with a filter that finds no employees.

BEGIN
   update_by_filter (filter_nt ('S%', 'E%', '%A%', 'XXXXX'));
END;
/

** What's a "pseudo-collection"? It's a data structure created implicitly by PL/SQL for us. The "pseudo" indicates that it doesn't have all the features of a "normal" collection.

In the case of SQL%BULK_ROWCOUNT, it has no methods defined on it (COUNT, FIRST, LAST, etc.). It always has the same index values defined in it as the bind array, regardless of whether it is densely or sparsely filled.

Fill in the Blanks

In the block below replace the #FINISH# tags with code so that after execution, we see the total number of rows deleted from the employees table, and the number of rows deleted in departments 10 and 100.

DECLARE
   TYPE ids_t IS TABLE OF employees.department_id%TYPE;
   l_ids ids_t := ids_t (10, 100);
BEGIN
   #FINISH#
      DELETE FROM employees
            WHERE department_id = l_ids (l_index);

   #FINISH#
   ROLLBACK;
END;

Exercise 8:

Write the rest of the procedure whose signature is shown below. The procedure uses FORALL to update the salaries of all employees in each department to the corresponding value in the salaries array. Afterwards, display the total number of rows modified. Raise the PROGRAM_ERROR exception if any of the update statements (for a specific department ID) change less than 2 rows.

PROCEDURE update_salaries (
   department_ids_in IN DBMS_SQL.NUMBER_TABLE,
   salaries_in IN DBMS_SQL.NUMBER_TABLE)

Errors and FORALL

So many errors can occur when you are trying to change rows in a table. Constraint violations, values too large for columns....now add to that the fact that with FORALL you are executing that DML statement many times over. Managing errors with FORALL is a tricky and important thing to do!

Before diving into the error-related features of FORALL, let's review some important points about transactions, errors and exceptions in the world of PL/SQL.

• If the SQL engine raises an error back to the PL/SQL engine, that does not cause an automatic rollback of previously successful DML statements. They are still waiting to be committed or rolled back.
• If an exception goes unhandled out of PL/SQL (back to, say, SQL*Plus or SQL Developer or your APEX app), then a rollback will always be performed.
• Each SQL statement is atomic ("all or nothing"), unless you use the LOG ERRORS feature. In other words, if your update statement finds 100 rows to change, and as it is changing the 100th of them, it hits an error, the changes to all 100 rows are reversed.
• LOG ERRORS is a SQL feature that allows you to suppress errors at the row level, avoiding the "all or nothing" scenario. We are not exploring LOG ERRORS in this tutorial. I just wanted to make sure you are aware. :-) Oh and search LiveSQL for "log errors" to see a script or two on that feature.

What does this for FORALL? That, by default, the first time the SQL engine encounters an error processing the DML statement passed to it from FORALL, it stops and passes the error back to the PL/SQL engine. No further processing is done, but also any statements completed successfully by the FORALL are still waiting to be committed or rolled back.

You can see this behavior in the code below (note the constraint on the size of numbers in the n column: 1 and 10 are OK, 100 is too large).

CREATE TABLE mynums (n NUMBER (2))
/

DECLARE
   TYPE numbers_t IS TABLE OF NUMBER;
   l_numbers   numbers_t := numbers_t (1, 10, 100);
BEGIN
   FORALL indx IN 1 .. l_numbers.COUNT
      INSERT INTO mynums (n)
         VALUES (l_numbers (indx));
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT || ' rows.');
      DBMS_OUTPUT.put_line (SQLERRM);
      ROLLBACK;
END;
/

DECLARE
   TYPE numbers_t IS TABLE OF NUMBER;
   l_numbers   numbers_t := numbers_t (100, 10, 1);
BEGIN
   FORALL indx IN 1 .. l_numbers.COUNT
      INSERT INTO mynums (n)
         VALUES (l_numbers (indx));
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT || ' rows.');
      DBMS_OUTPUT.put_line (SQLERRM);
      ROLLBACK;
END;
/

DROP TABLE mynums
/

The first block updated 2 rows, the second block updated 0 rows. You see here the "shortcutting" that the SQL engine does with your FORALL DML statement and the array of bind variables.

This is entirely consistent with non-bulk behavior. By which I mean: if you have a block that executes an insert, then another insert, then a delete and finally an update, the PL/SQL and SQL engines will keep on going just as long as they can, stop when there is an error, and leave it to you, the developer, to decide what to do about it.

But there is a difference between a non-bulk multi-DML set of statements and FORALL: with the non-bulk approach, you can tell the PL/SQL engine to continue past a failed SQL statement by putting it inside a nested block, as in:

BEGIN
   BEGIN
      INSERT INTO ...
   EXCEPTION 
      WHEN OTHERS 
      THEN
          log_error ...
   END;

   BEGIN
      UPDATE ...
   EXCEPTION 
      WHEN OTHERS 
      THEN
          log_error ...
   END;
END;

Note: I am not recommending that you do surround each non-query DML statement in a nested table, trapping any exception, and then continuing. Not at all. I am saying: it is possible, and you can choose to do this if you'd like. What about with FORALL?

With FORALL, you are choosing to execute the same statement, many times over. It could be that you do want to stop your FORALL as soon as any statement fails. In which case, you are done. If, however, you want to keep on going, even if there is a SQL error for a particular set of bind variable values, you need to take advantage of the SAVE EXCEPTIONS clause.

Exercise 9:

Complete the block below as follows: use FORALL to update the salaries of employees whose IDs are in l_ids with the corresponding salary in l_salaries. Display the total number of employees modified. First, use values for salaries that will allow all statements to complete successfully (salary is defined as NUMBER (8,2). Then change the salary values to explore the different ways that FORALL deals with errors.

DECLARE
   TYPE numbers_nt IS TABLE OF NUMBER;
   l_ids numbers_nt := numbers_nt (101, 111, 131);
   l_salaries numbers_nt := numbers_nt (#FINISH#);
BEGIN
   #FINISH#
END;

Continuing Past Errors with SAVE EXCEPTIONS

Add the SAVE EXCEPTIONS clause to your FORALL statement when you want the PL/SQL runtime engine to execute all DML statements generated by the FORALL, even if one or more than fail with an error.

In the code below (which is, I admit, kind of silly, since it updates all the employee rows each time), I am going to get errors. You can't update a first_name to a string of 1000 or 3000 bytes. But without SAVE EXCEPTIONS we never get past the third element in the bind array. The employees table has 107 rows. How many were updated?

DECLARE  
   TYPE namelist_t IS TABLE OF VARCHAR2 (5000);  
  
   enames_with_errors   namelist_t  
      := namelist_t ('ABC',  
                     'DEF',  
                     RPAD ('BIGBIGGERBIGGEST', 1000, 'ABC'),  
                     'LITTLE',  
                     RPAD ('BIGBIGGERBIGGEST', 3000, 'ABC'),  
                     'SMITHIE');  
BEGIN  
   FORALL indx IN 1 .. enames_with_errors.COUNT  
      UPDATE employees  
         SET first_name = enames_with_errors (indx);    
EXCEPTION  
   WHEN OTHERS  
   THEN  
      DBMS_OUTPUT.put_line (  
         'Updated ' || SQL%ROWCOUNT || ' rows.');  
      DBMS_OUTPUT.put_line (SQLERRM);  
      ROLLBACK;  
END; 

Let's try that again with SAVE EXCEPTIONS. Below I ask that PL/SQL execute every generated statement no matter how of them fail, please! Now how many rows were updated? Notice that with SAVE EXCEPTIONS in place,

DECLARE  
   TYPE namelist_t IS TABLE OF VARCHAR2 (5000);  
  
   enames_with_errors   namelist_t  
      := namelist_t ('ABC',  
                     'DEF',  
                     RPAD ('BIGBIGGERBIGGEST', 1000, 'ABC'),  
                     'LITTLE',  
                     RPAD ('BIGBIGGERBIGGEST', 3000, 'ABC'),  
                     'SMITHIE');  
BEGIN  
   FORALL indx IN 1 .. enames_with_errors.COUNT SAVE EXCEPTIONS  
      UPDATE employees  
         SET first_name = enames_with_errors (indx);  
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (  
         'Updated ' || SQL%ROWCOUNT || ' rows.');  
      DBMS_OUTPUT.put_line (SQLERRM);  
      ROLLBACK;  
END;

Fill in the Blanks

In the block below replace the #FINISH# tags with code so that after execution, we see "Updated 2" and "ORA-24381: error(s) in array DML".

DECLARE  
   TYPE numbers_t IS TABLE OF NUMBER;  
  
   l_salaries   numbers_t  
      := numbers_t (100, 10000000, -900, 222888999);  
   l_ids         numbers_t  
      := numbers_t (107, 175, 133, 192); 
BEGIN
   #FINISH#  
      UPDATE employees  
         SET salary = l_salaries (indx)
         #FINISH#;  
EXCEPTION  
   WHEN OTHERS  
   THEN  
      DBMS_OUTPUT.put_line (  
         'Updated ' || SQL%ROWCOUNT || ' rows.');  
      DBMS_OUTPUT.put_line (SQLERRM);  
      ROLLBACK;  
END; 

Exercise 10:

Change the code you wrote in Exercise 9 so that the SQL engine executes the DML statement as many times as there are elements in the binding array. Notice how the error information changes.

SQL%BULK_EXCEPTIONS

If you want the PL/SQL engine to execute as many of the DML statements as possible, even if errors are raised along the way, add the SAVE EXCEPTIONS clause to the FORALL header. Then, if the SQL engine raises an error, the PL/SQL engine will save that information in a pseudo-collection** named SQL%BULK_EXCEPTIONS, and continue executing statements. When all statements have been attempted, PL/SQL then raises the ORA-24381 error.

You can—and should—trap that error in the exception section and then iterate through the contents of SQL%BULK_EXCEPTIONS to find out which errors have occurred. You can then write error information to a log table and/or attempt recovery of the DML statement.

It is a collection of records, each of which has two fields: ERROR_INDEX and ERROR_CODE.

The index field contains a sequentially generated integer, incremented with each statement execution in the SQL engine. For sequentially filled collections, this integer matches the index value of the variable in the bind array. For sparsely-filled collections (see the modules on INDICES OF and VALUES OF for more details), you will have to write special-purpose code to "link back" the nth statement to its index value in the bind array.

ERROR_CODE is the value returned by SQLCODE at the time the error occurred. Note that this collection does not include the error message.

Let's go back to the same block used to show the effects of SAVE EXCEPTIONS, but now also take advantage of SQL%BULK_EXCEPTIONS.

DECLARE  
   TYPE namelist_t IS TABLE OF VARCHAR2 (5000);  
  
   enames_with_errors   namelist_t  
      := namelist_t ('ABC',  
                     'DEF',  
                     RPAD ('BIGBIGGERBIGGEST', 1000, 'ABC'),  
                     'LITTLE',  
                     RPAD ('BIGBIGGERBIGGEST', 3000, 'ABC'),  
                     'SMITHIE');  
BEGIN  
   FORALL indx IN 1 .. enames_with_errors.COUNT SAVE EXCEPTIONS  
      UPDATE employees  
         SET first_name = enames_with_errors (indx);  
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (  
         'Updated ' || SQL%ROWCOUNT || ' rows.');  
      DBMS_OUTPUT.put_line (SQLERRM); 

      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
      LOOP
         DBMS_OUTPUT.put_line (
               'Error '
            || indx
            || ' occurred on index '
            || SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
            || ' attempting to update name to "'
            || enames_with_errors (
                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX)
            || '"');
         DBMS_OUTPUT.put_line (
               'Oracle error is '
            || SQLERRM (
                  -1 * SQL%BULK_EXCEPTIONS (indx).ERROR_CODE));
      END LOOP;
 
      ROLLBACK;  
END;

Notice that I multiply the error code in SQL%BULK_EXCEPTIONS by -1 before passing it to SQLERRM. Here's why I do that:

• SQLERRM not only returns the current error message. If you pass it an error code, it returns the error message for that code.
• But it expects a negative value for all but two error codes - 0 (no error) and 100 (the ANSI-standard NO_DATA_FOUND error code).
• For reasons that may never be known, the value for the error code stored in the pseudo-collection is not negative.

** What's a "pseudo-collection"? It's a data structure created implicitly by PL/SQL for us. The "pseudo" indicates that it doesn't have all the features of a "normal" collection.

In the case of SQL%BULK_EXCEPTIONS, it has just one method defined on it: COUNT. It is always filled sequentially from index value 1. The number of elements in this pseudo-collection matches the number of times the SQL engine encountered an error.

Exercise 11:

Change the code you wrote in Exercise 10 as follows:

1. Expand the number of employee IDs to update to 5.
2. Change the array of salary values so that at least 2 of the updates will fail.
3. Change the exception handler to show which of the attempts to update failed, the error code, and the error message associated with that code.

Binding Sparse Arrays with INDICES OF

The INDICES OF clause allows you to use sparsely-filled bind arrays in FORALL. This is necessary because with the "usual" FORALL syntax of:

FORALL indx IN low_value .. high_value

The PL/SQL engine assumes that every index value between the low and high values are defined. If any of them are "missing" (not assigned a value), then you get an error. Not the NO_DATA_FOUND you might expect (which you get when you try to "read" an element at an undefined index value), but the ORA-22160 error, as you will see when you run the code below.

DECLARE
   TYPE ids_t IS TABLE OF employees.department_id%TYPE;
   l_ids ids_t := ids_t (10, 50, 100);
BEGIN
   /* Now I remove the "in between" element */
   l_ids.DELETE (2);

   FORALL l_index IN 1 .. l_ids.COUNT
      DELETE FROM employees
            WHERE department_id = l_ids (l_index);

   DBMS_OUTPUT.put_line ('Rows modified = ' || SQL%ROWCOUNT);
EXCEPTION
   WHEN OTHERS 
   THEN       
      DBMS_OUTPUT.put_line ('Rows modified = ' || SQL%ROWCOUNT);
      DBMS_OUTPUT.put_line ('Error = ' || SQLERRM);
      ROLLBACK;
END;

How do you get around this problem? Certainly, you could "densify" your collection before binding it to FORALL. And prior to 10.2 you would have had to do this. But as of Oracle Database 10g Release 2, you can use INDICES OF (or VALUES OF - covered in the next module).

With the INDICES OF clause, you say, in essence: "Just bind the elements at the index values that are defined." Or to put it more simply: "Please skip over any undefined index values." Note that the collection referenced in the INDICES OF clause must be indexed by PLS_INTEGER; you cannot use string-indexed collections. But the datatype of the elements in the collection can be anything - they are not referenced at all with INDICES OF.

Let's start with the simplest usage of INDICES OF: the indexing collection is the same as the bind collection. In the block below, I populate the l_employees associative array using ndex values 1, 100, 500 - rather than, say, 1-3. That's not densely filled!

I then use INDICES OF to tell the PL/SQL engine to only use the defined indices of l_employees, and all is good. This is the simplest form of INDICES OF: using the bind array in the INDICES OF clause.

DECLARE  
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE  
                           INDEX BY PLS_INTEGER;  
  
   l_employees   employee_aat;  
BEGIN  
   l_employees (1) := 137;  
   l_employees (100) := 126;  
   l_employees (500) := 147;  
  
   FORALL l_index IN INDICES OF l_employees  
      UPDATE employees  
         SET salary = 10000  
       WHERE employee_id = l_employees (l_index);  
  
   DBMS_OUTPUT.put_line ('Rows modified = ' || SQL%ROWCOUNT); 
   ROLLBACK; 
END; 

Here is a more interesting use of INDICES OF: I populate a second collection to serve as an "index" into the bind array. I also include the BETWEEN clause, which can be used to further restrict the set of elements in the indexing collection that will be used to bind values into the DML statement. It doesn't matter one bit what values are assigned to the elements in the indexing array - just which index values are defined.

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;

   l_employees          employee_aat;

   TYPE boolean_aat IS TABLE OF BOOLEAN
      INDEX BY PLS_INTEGER;

   l_employee_indices   boolean_aat;
BEGIN
   l_employees (1) := 137;
   l_employees (100) := 126;
   l_employees (500) := 147;
   --
   l_employee_indices (1) := FALSE;
   l_employee_indices (500) := TRUE;
   l_employee_indices (799) := NULL;

   FORALL l_index IN INDICES OF l_employee_indices BETWEEN 1 AND 600
      UPDATE employees
         SET salary = 10001
       WHERE employee_id = l_employees (l_index);

   DBMS_OUTPUT.put_line ('Rows modified = ' || SQL%ROWCOUNT);

   FOR rec IN (SELECT employee_id
                 FROM employees
                WHERE salary = 10001)
   LOOP
      DBMS_OUTPUT.put_line (rec.employee_id);
   END LOOP;

   ROLLBACK;
END;

You might be thinking to yourself: "Won't INDICES OF work just fine with densely-filled collections as well? why not use it all time?"

Yes, INDICES OF works just fine with dense or sparse collections, and there doesn't seem to be any performance difference. So why not use it all the time? One thing to consider is that using INDICES OF might hide a bug. What if your collection really should be dense but something went wrong and now it has undefined elements? In this case, your code should raise an error but it will not.

Not good. And that's why I suggest you use the syntax that is appropriate to your program and context.

A common and most excellent use case for INDICES OF is to help manage bulk transactions that involve more than one DML statement. For example, you do a FORALL-UPDATE, then two FORALL-INSERTs. If any of the attempted updates fail, you need to not perform the associated inserts. One way to do this is to delete elements from the FORALL-INSERT bind arrays for failed updates. You will then have a sparse collection and will want to switch to INDICES OF.

We do not cover this complex scenario in the tutorial, but this LiveSQL script shows you the basic idea: Converting from Row-by-Row to Bulk Processing, http://bit.ly/bulkconv.

Exercise 12:

Change the procedure below so that no assumptions are made about the contents of the ids_in array and the procedure completes without raising an exception. Assumptions you could make, but should not, include:

• The collection is never empty.
• The first index defined in the collection is 1.
• The collection is dense.
• The collection is sparse.

CREATE OR REPLACE TYPE numbers_t IS TABLE OF NUMBER
/

CREATE OR REPLACE PROCEDURE update_emps (ids_in IN numbers_t)
IS
BEGIN
   FORALL l_index IN 1 .. ids_in.COUNT
      UPDATE employees
         SET hire_date = SYSDATE
       WHERE employee_id = ids_in (l_index);

   ROLLBACK;
END;
/

Exercise 13 (advanced):

Got some time on your hands? Ready to explore all that INDICES OF can do? OK, let's go! Write a block of code that does the following:

• Populates a collection using BULK COLLECT with all the rows and columns of the employees table, ordered by employee ID: the bind array.
• Declare an associative array that can be used with INDICES OF.
• Populate one collection of that type with index values from the bind array for all employees with salary = 2600: index array 1.
• Populate a second collections of that type with index values from the bind array of those employees who were hired in 2004: index array 2.
• Write a FORALL statements that uses index array 1 to set the salaries of those employees to 2626.
• Write a FORALL statements that uses index array 2 to set the hire dates of those employees to January 1 2014.
• Include code to verify that the changes were made properly.

Binding Sparse Arrays with VALUES OF

The VALUES OF clause, like its "sister" INDICES OF, makes it easier to use FORALL with bind arrays that are sparse - or with bind arrays from which you need to select out specific elements for binding. With VALUES OF, you specify a collection whose element values (not the index values) specify the index values in the bind arrays that will be used to generate DML statements in the FORALL. Sound complicated? It is, sort of. It is another level of "indirectness" from INDICES OF, and not as commonly used.

Notice in the block below that the three element values are -77, 13067 and 1070. These in turn are index values in the l_employees array. And those elements in turn have the values 124, 123 and 129. You should therefore see in the output of the last step in the script that the employees with these 3 IDs now earn a $10000 salary.

DECLARE
   TYPE employee_aat IS TABLE OF employees.employee_id%TYPE
      INDEX BY PLS_INTEGER;

   l_employees         employee_aat;

   TYPE values_aat IS TABLE OF PLS_INTEGER
      INDEX BY PLS_INTEGER;

   l_employee_values   values_aat;
BEGIN
   l_employees (-77) := 134;
   l_employees (13067) := 123;
   l_employees (99999999) := 147;
   l_employees (1070) := 129;
   --
   l_employee_values (100) := -77;
   l_employee_values (200) := 13067;
   l_employee_values (300) := 1070;

   --
   FORALL l_index IN VALUES OF l_employee_values
      UPDATE employees
         SET salary = 10000
       WHERE employee_id = l_employees (l_index);

   DBMS_OUTPUT.put_line (SQL%ROWCOUNT);
END;

The datatype of elements in a collection used in the VALUES OF clause must be PLS_INTEGER. It also must be indexed by PLS_INTEGER.

You are much less likely to use VALUES OF than INDICES OF, but it's good to know that it's there.

Dynamic SQL and FORALL

Have you got a dynamic update, insert or delete sitting inside a loop? Don't worry....FORALL will still come to the rescue! Yes, that's right, you can put an EXECUTE IMMEDIATE statement inside FORALL, as well as static SQL. Let's take a look.

CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER
/

CREATE OR REPLACE TYPE namelist IS TABLE OF VARCHAR2 (15)
/

CREATE OR REPLACE PROCEDURE update_emps (col_in      IN VARCHAR2
                                       , empnos_in   IN numlist)
IS
   enames   namelist;
BEGIN
   FORALL indx IN empnos_in.FIRST .. empnos_in.LAST
      EXECUTE IMMEDIATE
            'UPDATE employees SET '
         || col_in
         || ' = '
         || col_in
         || ' * 1.1 WHERE employee_id = :1
         RETURNING last_name INTO :2'
         USING empnos_in (indx)
         RETURNING BULK COLLECT INTO enames;

   FOR indx IN 1 .. enames.COUNT
   LOOP
      DBMS_OUTPUT.put_line ('10% raise to ' || enames (indx));
   END LOOP;
END;
/

DECLARE
   l_ids   numlist := numlist (138, 147);
BEGIN
   update_emps ('salary', l_ids);
END;
/

Notice that I also used BULK COLLECT inside my FORALL statement, since I wanted get back from FORALL the last names of employees who got a raise. You need to include the RETURNING clause inside the dynamic DML statement and in the EXECUTE IMMEDIATE statement itself.

The bottom line: if you can execute the DML statement statically, you can do it dynamically, too. Same FORALL syntax, just a different way of expressing the SQL statement inside it.

Solutions to Exercises

Note that your solution might not match mine exactly, and that's just fine - as long as you use the bulk processing features correctly. :-)

Exercise 1:

Write a store procedure that accepts a department ID, uses BULK COLLECT to retrieve all employees in that department, and displays their first name and salary. Then write an anonymous block to run that procedure for department 100.

Solution to 1:

CREATE OR REPLACE PROCEDURE show_emps_in_dept (
   department_id_in IN employees.department_id%TYPE)
IS
   TYPE two_cols_rt IS RECORD (
      first_name   employees.first_name%TYPE,
      salary       employees.salary%TYPE
   );

   TYPE employee_info_t IS TABLE OF two_cols_rt;

   l_employees   employee_info_t;
BEGIN
   SELECT first_name, salary
     BULK COLLECT INTO l_employees
     FROM employees
    WHERE department_id = department_id_in;

   FOR indx IN 1 .. l_employees.COUNT
   LOOP
      DBMS_OUTPUT.PUT_LINE (l_employees(indx).first_name || ' makes ' ||
                            l_employees(indx).salary);
   END LOOP;
END;
/

BEGIN
   show_emps_in_dept (100);
END;
/

Exercise 2

Write an anonymous block that fetches (using BULK COLLECT) only the last name and salary from the employees table 5 rows at a time, and then displays that information. Make sure 107 names and salaries are shown!

Solution to 2:

DECLARE
   c_limit       PLS_INTEGER := 5;

   CURSOR employees_cur
   IS
      SELECT last_name, salary
        FROM employees;

   TYPE employee_ids_t IS TABLE OF employees_cur%ROWTYPE;

   l_employees   employee_ids_t;
BEGIN
   OPEN employees_cur;

   LOOP
      FETCH employees_cur BULK COLLECT INTO l_employees LIMIT c_limit;

      EXIT WHEN l_employees.COUNT = 0;

      FOR indx IN 1 .. l_employees.COUNT
      LOOP
         DBMS_OUTPUT.put_line (
            l_employees (indx).last_name || ' - ' || l_employees (indx).salary);
      END LOOP;
   END LOOP;
END;

Exercise 3

This exercise has two parts (and for this exercise assume that the employees table has 1M rows with data distributed equally amongst departments: (1) Write an anonymous block that contains a cursor FOR loop that does not need to be converted to using BULK COLLECT. (2) Write an anonymous block that contains a cursor FOR loop that does need to use BULK COLLECT (assume it cannot be rewritten in "pure" SQL).

Solution to 3:

BEGIN
   /* There is no need to convert to bulk collect because the PL/SQL
      compiler will automatically optimize the CFL to run "like" a
      BULK COLLECT with limit set to 100. */
   FOR rec IN (SELECT *
                 FROM employees
                WHERE department_id = 50)
   LOOP
      DBMS_OUTPUT.put_line (rec.last_name || ' - ' || rec.salary);
   END LOOP;
END;
/

BEGIN
   /* This loop now has a non-query DML statement inside it. And lots
      of employees in each department. So this row by row update is
      going to be slow. The compiler will still optimize the cursor FOR
      loop to be like a BULK COLLECT, but the row-by-row update will
      still be slow. 

      Bottom line: loop with non-query DML must be converted to
      "pure" SQL or a BULK COLLECT - FORALL combo! */
   FOR rec IN (SELECT employee_id
                 FROM employees
                WHERE department_id = 50)
   LOOP
      UPDATE employees
         SET salary = salary * 1.1
       WHERE employee_id = rec.employee_id;
   END LOOP;
END;
/

Exercise 4:

Write an anonymous block that deletes all the rows in the employees table for department 50 and returns all the employee IDs and the last names of deleted rows. Then display those values using DBMS_OUTPUT.PUT_LINE. Finally, you might want to rollback. That will make it easier to test your code - and continue on with the tutorial.

Solution to 4:

I offer two solutions below. One has two separate collections of scalar values to hold the data coming back from the RETURNING clause. The second uses a collection of records. Both work just fine!

DECLARE
   TYPE ids_t IS TABLE OF employees.employee_id%TYPE;

   l_ids     ids_t;

   TYPE names_t IS TABLE OF employees.last_name%TYPE;

   l_names   names_t;
BEGIN
   DELETE FROM employees
         WHERE department_id = 50
     RETURNING employee_id, last_name
          BULK COLLECT INTO l_ids, l_names;

   FOR indx IN 1 .. l_ids.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_ids (indx) || ' - ' || l_names (indx));
   END LOOP;

   ROLLBACK;
END;
/

DECLARE
   TYPE two_cols_rt IS RECORD
   (
      employee_id   employees.employee_id%TYPE,
      last_name     employees.last_name%TYPE
   );

   TYPE employee_info_t IS TABLE OF two_cols_rt;

   l_emps   employee_info_t;
BEGIN
   DELETE FROM employees
         WHERE department_id = 50
     RETURNING employee_id, last_name
          BULK COLLECT INTO l_emps;

   FOR indx IN 1 .. l_emps.COUNT
   LOOP
      DBMS_OUTPUT.put_line (
         l_emps (indx).employee_id || ' - ' || l_emps (indx).last_name);
   END LOOP;

   ROLLBACK;
END;
/

Exercise 5:

Write the rest of the procedure whose signature is shown below. Use BULK COLLECT to fetch all the last names from employees identified by that WHERE clause and return the collection. Then write an anonymous block to test your procedure: pass different WHERE clauses, and display names retrieved.

PROCEDURE get_names (
   where_in IN VARCHAR2,
   names_out OUT DBMS_SQL.VARCHAR2_TABLE)

Solution to 5:

CREATE OR REPLACE PROCEDURE get_names (
   where_in    IN     VARCHAR2,
   names_out      OUT DBMS_SQL.varchar2_table)
IS
BEGIN
   EXECUTE IMMEDIATE '
   SELECT last_name
     FROM employees WHERE ' || where_in
      BULK COLLECT INTO names_out;
END;
/

DECLARE
   l_names   DBMS_SQL.varchar2_table;
BEGIN
   get_names ('department_id = 100', l_names);
   DBMS_OUTPUT.put_line ('For department_id = 100');

   FOR indx IN 1 .. l_names.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_names (indx));
   END LOOP;

   get_names ('salary > 15000', l_names);
   DBMS_OUTPUT.put_line ('For salary > 15000');

   FOR indx IN 1 .. l_names.COUNT
   LOOP
      DBMS_OUTPUT.put_line (l_names (indx));
   END LOOP;
END;
/

Exercise 6:

Write an anonymous block that uses BULK COLLECT to populate two collections, one with the employee Id and the other with the employee's last name. Use those collections in a FORALL statement to update the employee's last name to the UPPER of the name in the name collection, for each employee ID. Note: clearly, you do not need BULK COLLECT and FORALL to do this. Please pretend. :-)

Solution to 6:

DECLARE
   TYPE ids_t IS TABLE OF employees.employee_id%TYPE;

   l_ids     ids_t;

   TYPE names_t IS TABLE OF employees.last_name%TYPE;

   l_names   names_t;
BEGIN
   SELECT employee_id, last_name
     BULK COLLECT INTO l_ids, l_names
     FROM employees;

   FORALL indx IN 1 .. l_ids.COUNT
      UPDATE employees
         SET last_name = UPPER (l_names (indx))
       WHERE employee_id = l_ids (indx);

   ROLLBACK;
END;

Exercise 7:

Write an anonymous block that uses BULK COLLECT to populate two collections, one with the employee Id and the other with the employee's last name. Use those collections in a FORALL statement to update the employee's last name to the UPPER of the name in the name collection, for each employee ID. Note: clearly, you do not need BULK COLLECT and FORALL to do this. Please pretend. :-)

Solution to 7:

Again, in two forms: one using two separate collections, the other using a collection of records.

DECLARE
   TYPE ids_t IS TABLE OF employees.employee_id%TYPE;

   l_ids     ids_t;

   TYPE names_t IS TABLE OF employees.last_name%TYPE;

   l_names   names_t;
BEGIN
   SELECT employee_id, last_name
     BULK COLLECT INTO l_ids, l_names
     FROM employees;

   FORALL indx IN 1 .. l_ids.COUNT
      UPDATE employees
         SET last_name = UPPER (l_names (indx))
       WHERE employee_id = l_ids (indx);

   ROLLBACK;
END;
/

DECLARE
   TYPE two_cols_rt IS RECORD
   (
      employee_id   employees.employee_id%TYPE,
      last_name     employees.last_name%TYPE
   );

   TYPE employee_info_t IS TABLE OF two_cols_rt;

   l_emps   employee_info_t;
BEGIN
   SELECT employee_id, last_name
     BULK COLLECT INTO l_emps
     FROM employees;

   FORALL indx IN 1 .. l_ids.COUNT
      UPDATE employees
         SET last_name = UPPER (l_emps (indx).last_name)
       WHERE employee_id = l_emps (indx).employee_id;

   ROLLBACK;
END;
/

Exercise 8:

Write the rest of the procedure whose signature is shown below. The procedure uses FORALL to update the salaries of all employees in each department to the corresponding value in the salaries array. Afterwards, display the total number of rows modified. Raise the PROGRAM_ERROR exception if any of the update statements (for a specific department ID) change less than 2 rows.

PROCEDURE update_salaries (
   department_ids_in IN DBMS_SQL.NUMBER_TABLE,
   salaries_in IN DBMS_SQL.NUMBER_TABLE)

Solution to 8:

Exercise 9:

Complete the block below as follows: use FORALL to update the salaries of employees whose IDs are in l_ids with the corresponding salary in l_salaries. Display the total number of employees modified. First, use values for salaries that will allow all statements to complete successfully (salary is defined as NUMBER (8,2). Then change the salary values to explore the different ways that FORALL deals with errors.

DECLARE
   TYPE numbers_nt IS TABLE OF NUMBER;
   l_ids numbers_nt := numbers_nt (101, 111, 131);
   l_salaries numbers_nt := numbers_nt (#FINISH#);
BEGIN
   #FINISH#
END;

Solution to 9:

DECLARE
   /* All valid, three rows should be updated. */
   TYPE numbers_nt IS TABLE OF NUMBER;

   l_ids        numbers_nt := numbers_nt (101, 111, 131);
   l_salaries   numbers_nt := numbers_nt (10000, 11000, 12000);
BEGIN
   FORALL indx IN 1 .. l_ids.COUNT
      UPDATE employees
         SET salary = l_salaries (indx)
       WHERE employee_id = l_ids (indx);

   DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT);
   ROLLBACK;
END;
/

DECLARE
   /* First one too big so nothing updated. */
   TYPE numbers_nt IS TABLE OF NUMBER;

   l_ids        numbers_nt := numbers_nt (101, 111, 131);
   l_salaries   numbers_nt := numbers_nt (1000000, 11000, 12000);
BEGIN
   FORALL indx IN 1 .. l_ids.COUNT
      UPDATE employees
         SET salary = l_salaries (indx)
       WHERE employee_id = l_ids (indx);

   DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT);
   ROLLBACK;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT);
      ROLLBACK;
END;
/

DECLARE
   /* Last one too big so two rows updated. */
   TYPE numbers_nt IS TABLE OF NUMBER;

   l_ids        numbers_nt := numbers_nt (101, 111, 131);
   l_salaries   numbers_nt := numbers_nt (10000, 11000, 1200000);
BEGIN
   FORALL indx IN 1 .. l_ids.COUNT
      UPDATE employees
         SET salary = l_salaries (indx)
       WHERE employee_id = l_ids (indx);

   DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT);
   ROLLBACK;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT);
      ROLLBACK;
END;
/

Exercise 10:

Change the code you wrote in Exercise 9 so that the SQL engine executes the DML statement as many times as there are elements in the binding array. Notice how the error information changes.

Solution to 10:

DECLARE
   /* Second value is too big but since I added SAVE EXCEPTIONS,
      we still see two rows updated - and a generic error message
      for the "generic" ORA-24381. */
   TYPE numbers_nt IS TABLE OF NUMBER;

   l_ids        numbers_nt := numbers_nt (101, 111, 131);
   l_salaries   numbers_nt := numbers_nt (10000, 1100000, 12000);
BEGIN
   FORALL indx IN 1 .. l_ids.COUNT SAVE EXCEPTIONS
      UPDATE employees
         SET salary = l_salaries (indx)
       WHERE employee_id = l_ids (indx);

   DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT);
   ROLLBACK;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
      DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT);
      ROLLBACK;
END;
/

Exercise 11:

Change the code you wrote in Exercise 10 as follows:

1. Expand the number of employee IDs to update to 5.
2. Change the array of salary values so that at least 2 of the updates will fail.
3. Change the exception handler to show which of the attempts to update failed, the error code, and the error message associated with that code.

Solution to 11:

DECLARE
   /* Second value is too big but since I added SAVE EXCEPTIONS,
      we still see two rows updated - and a generic error message. */
   TYPE numbers_nt IS TABLE OF NUMBER;

   l_ids        numbers_nt := numbers_nt (117, 101, 111, 131, 143);
   l_salaries   numbers_nt := numbers_nt (10000, 1100000, 12000, 6669999, 5000);
BEGIN
   FORALL indx IN 1 .. l_ids.COUNT SAVE EXCEPTIONS
      UPDATE employees
         SET salary = l_salaries (indx)
       WHERE employee_id = l_ids (indx);

   DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT);
   ROLLBACK;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Updated ' || SQL%ROWCOUNT || ' rows.');  
      DBMS_OUTPUT.put_line (SQLERRM); 

      FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
      LOOP
         DBMS_OUTPUT.put_line (
               'Error '
            || indx
            || ' - array index '
            || SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX
            || ' attempting to update salary to '
            || l_salaries (
                  SQL%BULK_EXCEPTIONS (indx).ERROR_INDEX));
         DBMS_OUTPUT.put_line (
               'Oracle error is '
            || SQLERRM (
                  -1 * SQL%BULK_EXCEPTIONS (indx).ERROR_CODE));
      END LOOP;
 
      ROLLBACK; 
END;

Exercise 12:

Change the procedure below so that no assumptions are made about the contents of the ids_in array and the procedure completes without raising an exception. Assumptions you could make, but should not, include:

• The collection is never empty.
• The first index defined in the collection is 1.
• The collection is dense.
• The collection is sparse.

CREATE OR REPLACE TYPE numbers_t IS TABLE OF NUMBER
/

CREATE OR REPLACE PROCEDURE update_emps (ids_in IN numbers_t)
IS
BEGIN
   FORALL l_index IN 1 .. ids_in.COUNT
      UPDATE employees
         SET hire_date = SYSDATE
       WHERE employee_id = ids_in (l_index);

   ROLLBACK;
END;
/

Solution to 12:

CREATE OR REPLACE TYPE numbers_t IS TABLE OF NUMBER
/

/* This version doesn't assume the first defined index value is 1,
   and it handles empty collections just fine, but it does assume the 
   collection is densely filled. */

CREATE OR REPLACE PROCEDURE update_emps (ids_in IN numbers_t)
IS
BEGIN
   FORALL l_index IN ids_in.FIRST .. ids_in.LAST
      UPDATE employees
         SET hire_date = SYSDATE
       WHERE employee_id = ids_in (l_index);

   ROLLBACK;
END;
/

CREATE OR REPLACE PROCEDURE test_update_emps
IS
   l_ids   numbers_t := numbers_t (111, 121, 132);
BEGIN
   BEGIN
      /* This is OK */
      update_emps (l_ids);
      DBMS_OUTPUT.put_line ('Success Dense');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SQLERRM);
   END;

   BEGIN
      /* But not when it is sparse. */
      l_ids := numbers_t (111, 121, 132);
      l_ids.delete (2);
      update_emps (l_ids);
      DBMS_OUTPUT.put_line ('Success Sparse');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Sparse ' || SQLERRM);
   END;

   BEGIN
      /* And not when it is empty. */
      l_ids.delete;
      update_emps (l_ids);
      DBMS_OUTPUT.put_line ('Success Empty');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Empty ' || SQLERRM);
   END;
END;
/

BEGIN
   test_update_emps;
END;
/

/* Switch to INDICES OF and all is good. */

CREATE OR REPLACE PROCEDURE update_emps (ids_in IN numbers_t)
IS
BEGIN
   FORALL l_index IN INDICES OF ids_in
      UPDATE employees
         SET hire_date = SYSDATE
       WHERE employee_id = ids_in (l_index);

   ROLLBACK;
END;
/

BEGIN
   test_update_emps;
END;
/

Exercise 13 (advanced):

Got some time on your hands? Ready to explore all that INDICES OF can do? OK, let's go! Write a block of code that does the following:

• Populates a collection using BULK COLLECT with all the rows and columns of the employees table, ordered by employee ID: the bind array.
• Declare an associative array that can be used with INDICES OF.
• Populate one collection of that type with index values from the bind array for all employees with salary = 2600: index array 1.
• Populate a second collections of that type with index values from the bind array of those employees who were hired in 2004: index array 2.
• Write a FORALL statements that uses index array 1 to set the salaries of those employees to 2626.
• Write a FORALL statements that uses index array 2 to set the hire dates of those employees to January 1 2014.
• Include code to verify that the changes were made properly.

Solution to 13:

DECLARE
   TYPE employee_aat IS TABLE OF employees%ROWTYPE
      INDEX BY PLS_INTEGER;

   l_employees   employee_aat;

   TYPE index_aat IS TABLE OF BOOLEAN
      INDEX BY PLS_INTEGER;

   l_indices1    index_aat;
   l_indices2    index_aat;
BEGIN
     SELECT *
       BULK COLLECT INTO l_employees
       FROM employees
   ORDER BY employee_id;

   DBMS_OUTPUT.put_line ('*** Before changes:');

   FOR indx IN 1 .. l_employees.COUNT
   LOOP
      IF l_employees (indx).salary = 2600
      THEN
         l_indices1 (indx) := NULL;
         DBMS_OUTPUT.put_line (
            'ID ' || l_employees (indx).employee_id || ' has salary 2600');
      END IF;

      IF TO_CHAR (l_employees (indx).hire_date, 'YYYY') = '2004'
      THEN
         l_indices2 (indx) := NULL;
         DBMS_OUTPUT.put_line (
            'ID ' || l_employees (indx).employee_id || ' hired in 2004');
      END IF;
   END LOOP;

   FORALL indx IN INDICES OF l_indices1
      UPDATE employees
         SET salary = 2626
       WHERE employee_id = l_employees (indx).employee_id;

   FORALL indx IN INDICES OF l_indices2
      UPDATE employees
         SET hire_date = DATE '2014-01-01'
       WHERE employee_id = l_employees (indx).employee_id;

   DBMS_OUTPUT.put_line ('*** After changes:');

   FOR rec IN (  SELECT *
                   FROM employees
               ORDER BY employee_id)
   LOOP
      IF rec.salary = 2626
      THEN
         DBMS_OUTPUT.put_line ('ID ' || rec.employee_id || ' has salary 2626');
      END IF;

      IF rec.hire_date = DATE '2014-01-01'
      THEN
         DBMS_OUTPUT.put_line ('ID ' || rec.employee_id || ' hired 2014-01-01');
      END IF;
   END LOOP;

   ROLLBACK;
END;

Oracle Dev Gym Workouts

The Oracle Dev Gym offers multiple choices quizzes, workouts and classes on a wide variety of Oracle Database topics. Find below a set of four workouts (three featuring content by Tim Hall) on FORALL and BULK COLLECT. They are a great follow-up and reinforcement for this tutorial.

BULK COLLECT by Tim Hall

Tim Hall of oracle-base.com fame explores the BULK COLLECT feature of PL/SQL, which allows you to retrieve multiple rows with a single fetch. Note that Tim's article also covers FORALL, which is for multi-row, non-query DML (inserts, updates, deletes) and will be explored in a separate workout. After you read his article and check out the documentation, it's time to take four quizzes written by Steven Feuerstein to test your knowledge of this feature.

https://devgym.oracle.com/pls/apex/dg/workout/bulk-collect.html

FORALL - Basic Concepts by Tim Hall

Tim Hall offers a comprehensive review of bulk processing in PL/SQL; this workout focuses in on FORALL, covering the basic concepts behind this powerful performance enhancer. We complement Tim's article with a link to documentation and FORALL quizzes from the Dev Gym library.

https://devgym.oracle.com/pls/apex/dg/workout/forall-basic-concepts.html

FORALL and SAVE EXCEPTIONS by Tim Hall

Tim Hall of Oracle-BASE.com offers a comprehensive review of bulk processing in PL/SQL in this workout's leading exercise. Drill down to the SAVE EXCEPTIONS section of Tim's article to explore how to handle exceptions that may be raised when FORALL executes. Check out the documentation for more details. Then finish up with quizzes from Steven Feuerstein on SAVE EXCEPTIONS. Go beyond FORALL basics with this workout!

https://devgym.oracle.com/pls/apex/dg/workout/forall-and-save-exceptions.html

An Hour of Bulk Processing Quizzes

Ten quizzes on FORALL and BULK COLLECT, ranging in difficulty from beginner to intermediate.

https://devgym.oracle.com/pls/apex/dg/workout/an-hour-of-bulk-processing-quizzes.html