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 data row by row. OceanBase Database Enterprise Edition in Oracle mode supports two core syntaxes: FORALL (for executing DML operations in bulk) and BULK COLLECT (for retrieving query results into a collection). When an operation involves multiple rows, performance can be significantly improved.

Example table

The following examples are based on the same table. Run 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 that executes each statement individually. This is because the SQL engine supports array binding.

Configuration requirements

Set the ob_enable_batched_multi_statement parameter to true. If it is set to false, the execution will be degraded to a FOR loop.

Limitations

The execution will be degraded to a FOR loop in the following cases:

• The IN OUT bind variable is used in dynamic SQL.
• The dynamic SQL is not one of the supported DML operations (INSERT, UPDATE, DELETE, or MERGE INTO).
• The error message for some invalid index usage differs from that in Oracle.

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 (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, 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 preserved.
      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: Continue executing when some DML operations fail. After the execution, use SQL%BULK_EXCEPTIONS to view the failed indices and error codes.

  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 the collection yourself and cannot directly reference variables from the outer scope.

  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. It can be used in SELECT INTO, FETCH, and RETURNING INTO statements.

Examples of BULK COLLECT

• SELECT INTO + BULK COLLECT: multiple columns are bulk-loaded 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: multiple rows are fetched from a cursor 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: columns are bulk-loaded 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;
  /
  

Using FORALL with BULK COLLECT

Use BULK COLLECT to retrieve keys into a collection and then use FORALL to execute DML operations in batches.

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