FORALL bulk execution

The FORALL statement is used to perform bulk execution of DML statements such as INSERT, UPDATE, DELETE, and MERGE INTO. Bulk execution depends on the arraybinding feature of the SQL engine.

The FORALL statement performs bulk execution of DML statements by using bulk binding of variables (BULK IN-BIND). Compared with executing each statement individually in a FOR loop, this approach can significantly improve performance.

Configuration requirements

To use the FORALL feature for batch execution, set the ob_enable_batched_multi_statement parameter to true. If this parameter is false, batch execution will fall back to FOR loop execution.

Example table

The following scenarios do not support batch execution and will fall back to FOR loop execution at runtime:

• Using IN OUT bound variables in dynamic SQL.
• Dynamic SQL statements that are not supported DML operations (INSERT, UPDATE, DELETE, MERGE INTO) will fall back to FOR loop execution.
• Error messages for some invalid index usage differ from those in Oracle.

obclient> CREATE TABLE employee_info (
    emp_id NUMBER,
    emp_name VARCHAR2(50),
    salary NUMBER
);

FORALL index IN { lower_bound .. upper_bound
                | INDICES OF collection [ BETWEEN lower_bound AND upper_bound ]
                | VALUES OF index_collection }
  [ SAVE EXCEPTIONS ]
  dml_statement;

Examples

• Use the FORALL statement to insert data in batches.

  obclient> CREATE TABLE employee_info (
      emp_id NUMBER,
      emp_name VARCHAR2(50),
      salary NUMBER
  );
  Query OK, 0 rows affected
  
  obclient> DECLARE
      TYPE emp_id_array IS TABLE OF NUMBER;
      TYPE emp_name_array IS TABLE OF VARCHAR2(50);
      TYPE salary_array IS TABLE OF NUMBER;
      emp_ids emp_id_array := emp_id_array(1, 2, 3, 4, 5);
      emp_names emp_name_array := emp_name_array('Alice', 'Bob', 'Charlie', 'David', 'Eve');
      salaries salary_array := salary_array(5000, 6000, 7000, 8000, 9000);
  BEGIN
      FORALL i IN 1..emp_ids.COUNT
          INSERT INTO employee_info VALUES (emp_ids(i), emp_names(i), salaries(i));
  
      DBMS_OUTPUT.PUT_LINE('Batch insert completed. ' || SQL%ROWCOUNT || ' records were inserted.');
  END;
  /
  

  The query result is as follows:

  obclient> SELECT * FROM employee_info;
  +--------+----------+--------+
  | EMP_ID | EMP_NAME | SALARY |
  +--------+----------+--------+
  |      1 | Alice    |   5000 |
  |      2 | Bob      |   6000 |
  |      3 | Charlie  |   7000 |
  |      4 | David    |   8000 |
  |      5 | Eve      |   9000 |
  +--------+----------+--------+
  5 rows in set
  

• When the collection is a sparse array (with empty elements), you can use the INDICES OF clause to process only the elements with values.

  obclient> DECLARE
      TYPE customer_array IS TABLE OF VARCHAR2(50);
      TYPE amount_array IS TABLE OF NUMBER;
  
      cust_tab customer_array := customer_array('Company1', NULL, 'Company3', NULL, 'Company5');
      amount_tab amount_array := amount_array(5000, NULL, 150, NULL, 4000);
  BEGIN
      FORALL i IN INDICES OF cust_tab
          INSERT INTO customer_orders (cust_name, amount)
          VALUES (cust_tab(i), amount_tab(i));
  
      DBMS_OUTPUT.PUT_LINE('Batch insert completed. ' || SQL%ROWCOUNT || ' records were inserted.');
  END;
  /
  

• Use the INDICES OF clause with the BETWEEN clause to specify the index range.

  obclient> DECLARE
      TYPE num_tab IS TABLE OF NUMBER;
      v_tb num_tab := num_tab(10, 20, 30);
      v_int NUMBER := 1;
  BEGIN
      v_tb := type_tab(1, 2, 3);
      v_tb.DELETE(2);  -- Delete the element at index 2 to create a sparse array
  
      FORALL i IN INDICES OF v_tb BETWEEN 1 AND 3
          INSERT INTO t_test1 VALUES (v_tb(i), v_int);
  
      DBMS_OUTPUT.PUT_LINE('Batch insert completed. ' || SQL%ROWCOUNT || ' records were inserted.');
  END;
  /
  

• Use the VALUES OF clause to execute batch operations based on the values of the index collection.

  obclient> DECLARE
      TYPE customer_array IS TABLE OF VARCHAR2(50);
      TYPE amount_array IS TABLE OF NUMBER;
      TYPE index_array IS TABLE OF PLS_INTEGER;
  
      cust_tab customer_array := customer_array('Company1', 'Company2', 'Company3', 'Company4', 'Company5');
      amount_tab amount_array := amount_array(5000, 0, 150, 4000, NULL);
      big_order_tab index_array := index_array();
  BEGIN
      -- Find the indices of orders with amounts greater than 2000
      FOR i IN cust_tab.FIRST .. cust_tab.LAST LOOP
          IF amount_tab(i) > 2000 THEN
              big_order_tab.EXTEND;
              big_order_tab(big_order_tab.LAST) := i;
          END IF;
      END LOOP;
  
      -- Insert only the large orders
      FORALL i IN VALUES OF big_order_tab
          INSERT INTO big_orders (cust_name, amount)
          VALUES (cust_tab(i), amount_tab(i));
  
      DBMS_OUTPUT.PUT_LINE('Batch insert completed. ' || SQL%ROWCOUNT || ' records were inserted.');
  END;
  /
  

• When you need to execute batch operations and want to continue even if some operations fail, you can use SAVE EXCEPTIONS.

  obclient> DECLARE
      TYPE emp_id_array IS TABLE OF NUMBER;
      TYPE emp_name_array IS TABLE OF VARCHAR2(50);
  
      emp_ids emp_id_array := emp_id_array(1, 2, 3, 4, 5);
      emp_names emp_name_array := emp_name_array('Alice', 'Bob', 'Charlie', 'David', 'Eve');
  
      dml_errors EXCEPTION;
      PRAGMA EXCEPTION_INIT(dml_errors, -24381);
  BEGIN
      FORALL i IN 1..emp_ids.COUNT SAVE EXCEPTIONS
          INSERT INTO employee_info VALUES (emp_ids(i), emp_names(i), 5000);
  
      DBMS_OUTPUT.PUT_LINE('Batch insert completed, with ' || SQL%ROWCOUNT || ' records inserted.');
  EXCEPTION
      WHEN dml_errors THEN
          DBMS_OUTPUT.PUT_LINE('There are ' || SQL%BULK_EXCEPTIONS.COUNT || ' errors:');
          FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
              DBMS_OUTPUT.PUT_LINE('Error ' || i || ': Index ' ||
                  SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                  ', Error code ' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
          END LOOP;
  END;
  /
  

• You can use the FORALL statement in a dynamic SQL string. The FORALL statement in dynamic SQL supports batch execution.

  obclient> DECLARE
      TYPE my_type IS TABLE OF VARCHAR2(10);
      var my_type := my_type('1', '2', 'a', 'b', 'c', '10');
  BEGIN
      EXECUTE IMMEDIATE 'FORALL i IN 1..var.COUNT
          INSERT INTO tab1(s1) VALUES(var(i))';
  
      DBMS_OUTPUT.PUT_LINE('Batch insert completed.');
  END;
  /
  

  Notice

  In dynamic SQL, the FORALL statement does not support binding variables in the IN OUT direction.

• Use the FORALL statement to batch update data.

  obclient> DECLARE
      TYPE emp_id_array IS TABLE OF NUMBER;
      TYPE salary_array IS TABLE OF NUMBER;
  
      emp_ids emp_id_array := emp_id_array(1, 2, 3);
      new_salaries salary_array := salary_array(5500, 6500, 7500);
  BEGIN
      FORALL i IN 1..emp_ids.COUNT
          UPDATE employee_info
          SET salary = new_salaries(i)
          WHERE emp_id = emp_ids(i);
  
      DBMS_OUTPUT.PUT_LINE('Batch update completed, with ' || SQL%ROWCOUNT || ' records updated.');
  END;
  /
  

• Use the FORALL statement to batch delete data.

  obclient> DECLARE
      TYPE dept_array IS TABLE OF NUMBER;
      depts dept_array := dept_array(10, 30, 70);
  BEGIN
      FORALL i IN depts.FIRST..depts.LAST
          DELETE FROM department_table WHERE dept_id = depts(i);
  
      DBMS_OUTPUT.PUT_LINE('Batch delete completed, with ' || SQL%ROWCOUNT || ' records deleted.');
  END;
  /