FORALL and BULK COLLECT

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

Sample table

The following examples are based on the same table. Execute the CREATE TABLE statement before running the examples.

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

FORALL statement

The FORALL statement executes DML statements by using bulk binding of variables, which is more efficient than the FOR loop, which executes DML statements one by one. The FORALL statement relies on array binding in the SQL engine.

Configuration requirements

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

Limitations

The FORALL statement is used for batch execution of DML statements such as insert, update, delete, and merge into. Batch execution relies on the array binding feature of the SQL engine.

In OceanBase Database, the FORALL statement is only allowed for batch execution in specific scenarios. In other cases, it will be downgraded to a FOR loop. The following situations will trigger this downgrade:

• 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 options are also not supported for batch execution.
• The insert, update, and delete statements cannot be batch executed because the SQL engine does not support array binding. The merge into statement is also not supported for batch execution.
• If the members of the collection are complex types (pl_extend, json, geometry, or user_defined_sql_type), batch execution is not supported.
• If non-batch-bound variables are complex types, they will be incorrectly identified as batch-bound variables during variable binding.
• If IN OUT bound variables are used in dynamic SQL statements, batch execution is not supported.
• If the dynamic SQL statement is not one of the supported DML operations (INSERT, UPDATE, DELETE, or MERGE INTO), batch execution is not supported.
• Some invalid index usage scenarios will result in error messages that 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 collection): When a collection contains null elements, only the indices with values are processed by DML operations.

  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, the rest are still executed. After the execution, you can use SQL%BULK_EXCEPTIONS to view the indices and error codes of the failed DML 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 need to declare a collection by yourself. You cannot directly reference variables in 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, the FORALL statement does not support IN OUT bind variables.

BULK COLLECT clause

The BULK COLLECT clause allows you to fetch multiple rows returned by an SQL statement into a PL/SQL collection. You can use the BULK COLLECT clause in SELECT INTO, FETCH, and RETURNING INTO statements.

Examples

• SELECT INTO + BULK COLLECT: You can use the BULK COLLECT clause in a SELECT INTO statement to fetch multiple rows 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 the BULK COLLECT clause in a FETCH statement to fetch multiple rows into a collection.

  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 the BULK COLLECT clause in a RETURNING INTO statement to write multiple 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 execute 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('Total rows updated: ' || SQL%ROWCOUNT);
END;
/