FORALL and BULK COLLECT

Bulk binding is a mechanism for transferring data in bulk between PL/SQL and SQL, reducing the overhead of fetching and inserting rows one by one. OceanBase Database Enterprise Edition in Oracle mode supports two core syntaxes: FORALL (for bulk DML execution) and BULK COLLECT (for bulk retrieval of query results into collections). When an operation involves multiple rows, performance is significantly improved.

Example table

The following examples are based on the same table. Please execute the table creation statement before running each example.

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

FORALL statement

The FORALL statement executes DML operations by using bulk binding of variables, which is more efficient than the FOR loop that executes each statement individually. This is because it relies on the SQL engine's array binding capabilities.

Configuration requirements

Set the ob_enable_batched_multi_statement parameter to true. If it is set to false, the FORALL statement will be executed in a FOR loop.

Limitations

The FORALL statement is used to batch execute DML statements such as insert, update, delete, and merge into. Batch execution depends on the arraybinding feature of the SQL engine.

In OceanBase Database, the FORALL statement is only allowed to batch execute in specific scenarios. In other scenarios, it will be executed in a FOR loop:

• Static SQL statements cannot be batch executed, while dynamic SQL statements can.
• If the bounds_clause of the FORALL statement is in the lower_bound..upper_bound format, batch execution is not supported. The indices of collection and values of index_collection clauses are also not supported for batch execution.
• The insert, update, and delete statements cannot be batch executed because the SQL engine does not support arraybinding. The merge into statement is also not supported for batch execution.
• If the members of the collection are complex types such as pl_extend, json, geometry, or user_defined_sql_type, batch execution is not supported.
• If non-batch bound variables are complex types, they may be mistakenly identified as batch bound variables during processing.
• If IN OUT bound variables are used in dynamic SQL statements, batch execution is not supported.
• If the dynamic SQL statement is not a supported DML statement such as INSERT, UPDATE, DELETE, or MERGE INTO, batch execution is not supported.
• Some error messages related to invalid index usage may differ from those in Oracle Database.

Syntax

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

• Bulk insert

  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('Inserted ' || SQL%ROWCOUNT || ' rows.');
  END;
  /
  

• Bulk update (You can use SQL%BULK_ROWCOUNT(i) to view the number of rows affected by the i-th DML operation.)

  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('Updated ' || SQL%ROWCOUNT || ' rows.');
  END;
  /
  

• Bulk delete

  obclient> DECLARE
      TYPE emp_id_array IS TABLE OF NUMBER;
      emp_ids emp_id_array := emp_id_array(1, 2, 3);
  BEGIN
      FORALL i IN emp_ids.FIRST..emp_ids.LAST
          DELETE FROM employee_info WHERE emp_id = emp_ids(i);
      DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
  END;
  /
  

• INDICES OF (sparse collections): When a collection contains null elements, DML operations are performed only on the non-null elements.

  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.DELETE(2);  -- Sparse: only indices 1 and 3 are retained.
      FORALL i IN INDICES OF v_tb BETWEEN 1 AND 3
          INSERT INTO employee_info (emp_id, emp_name, salary) VALUES (v_tb(i), 'X', v_int);
      DBMS_OUTPUT.PUT_LINE('Inserted ' || SQL%ROWCOUNT || ' rows.');
  END;
  /
  

• SAVE EXCEPTIONS: When some DML operations fail, continue to execute the remaining operations. After the execution, use SQL%BULK_EXCEPTIONS to view the indices and error codes of the failed operations.

  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('A', 'B', 'C', 'D', 'E');
      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);
  EXCEPTION
      WHEN dml_errors THEN
          FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
              DBMS_OUTPUT.PUT_LINE('Failed index: ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || ', Error code: ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE);
          END LOOP;
  END;
  /
  

• FORALL in dynamic SQL: In a dynamic block, you must declare a collection yourself. You cannot directly reference variables from the outer block.

  obclient> BEGIN
      EXECUTE IMMEDIATE 'DECLARE TYPE my_type IS TABLE OF VARCHAR2(10); var my_type := my_type(''1'', ''2'', ''3''); BEGIN FORALL i IN 1..var.COUNT INSERT INTO employee_info(emp_id, emp_name, salary) VALUES(var(i), ''X'', 1000); END;';
      DBMS_OUTPUT.PUT_LINE('Bulk insert completed.');
  END;
  /
  

  Notice

  In dynamic SQL, FORALL does not support IN OUT bind variables.

BULK COLLECT clause

The BULK COLLECT clause retrieves multiple rows returned by an SQL statement into a PL/SQL collection in one operation. You can use it with SELECT INTO, FETCH, and RETURNING INTO statements.

BULK COLLECT examples

• SELECT INTO + BULK COLLECT: You can use this clause to bulk load multiple columns into multiple collections.

  obclient> DECLARE
      TYPE num_tab IS TABLE OF employee_info.emp_id%TYPE;
      TYPE name_tab IS TABLE OF employee_info.emp_name%TYPE;
      v_ids   num_tab;
      v_names name_tab;
  BEGIN
      SELECT emp_id, emp_name BULK COLLECT INTO v_ids, v_names FROM employee_info ORDER BY emp_id;
      FOR i IN 1..v_ids.COUNT LOOP
          DBMS_OUTPUT.PUT_LINE(v_ids(i) || ': ' || v_names(i));
      END LOOP;
  END;
  /
  

• FETCH + BULK COLLECT: You can use this clause to fetch multiple rows from a cursor into a collection in one operation.

  obclient> DECLARE
      CURSOR c IS SELECT emp_id, emp_name, salary FROM employee_info ORDER BY emp_id;
      TYPE emp_tab IS TABLE OF c%ROWTYPE;
      v_emps emp_tab;
  BEGIN
      OPEN c;
      FETCH c BULK COLLECT INTO v_emps;
      CLOSE c;
      FOR i IN 1..v_emps.COUNT LOOP
          DBMS_OUTPUT.PUT_LINE(v_emps(i).emp_id || ' ' || v_emps(i).emp_name || ' ' || v_emps(i).salary);
      END LOOP;
  END;
  /
  

• RETURNING INTO + BULK COLLECT: You can use this clause to bulk load columns returned by a DML statement into a collection.

  obclient> DECLARE
      TYPE id_tab IS TABLE OF employee_info.emp_id%TYPE;
      v_ids id_tab;
  BEGIN
      DELETE FROM employee_info WHERE salary < 6000 RETURNING emp_id BULK COLLECT INTO v_ids;
      DBMS_OUTPUT.PUT_LINE('Deleted ' || v_ids.COUNT || ' rows');
  END;
  /
  

Use FORALL with BULK COLLECT

First, use BULK COLLECT to retrieve key values into a collection, then use FORALL to perform DML operations in batches based on the collection.

obclient> DECLARE
    TYPE id_tab IS TABLE OF employee_info.emp_id%TYPE;
    v_emp_ids id_tab;
BEGIN
    SELECT emp_id BULK COLLECT INTO v_emp_ids FROM employee_info WHERE salary < 8000;
    FORALL i IN 1..v_emp_ids.COUNT
        UPDATE employee_info SET salary = salary * 1.1 WHERE emp_id = v_emp_ids(i);
    DBMS_OUTPUT.PUT_LINE('Number of rows updated: ' || SQL%ROWCOUNT);
END;
/