How to Bind Arrays with DBMS_SQL.BIND_ARRAY

FORALL runs one DML statement for a whole collection when the statement is known at compile time. When the statement is built at run time, DBMS_SQL.BIND_ARRAY does the same: bind a collection to each placeholder, execute once, and every element becomes a row.

Code for This Guide

The main examples are in the examples/pkg-dbms-sql folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_sql.parse(c, 'insert into t values (:a, :b)', dbms_sql.native);
dbms_sql.bind_array(c, 'a', collection_a [, lower_index, upper_index]);
dbms_sql.bind_array(c, 'b', collection_b [, lower_index, upper_index]);
rows := dbms_sql.execute(c);

The collections are DBMS_SQL table types such as NUMBER_TABLE, VARCHAR2_TABLE, and DATE_TABLE.

Insert Three Rows in One Call

The example writes to a small work table:

Setup:

create table gate_log (flight_id number, gate varchar2(4));

Example:

declare
  c       integer := dbms_sql.open_cursor;
  v_ids   dbms_sql.number_table;
  v_gates dbms_sql.varchar2_table;
  v_rows  integer;
begin
  v_ids(1) := 2800; v_gates(1) := 'A1';
  v_ids(2) := 2801; v_gates(2) := 'B7';
  v_ids(3) := 2802; v_gates(3) := 'C3';
  dbms_sql.parse(c, 'insert into gate_log values (:id, :gate)', dbms_sql.native);
  dbms_sql.bind_array(c, 'id', v_ids);
  dbms_sql.bind_array(c, 'gate', v_gates);
  v_rows := dbms_sql.execute(c);                           -- one call, three rows
  dbms_output.put_line(v_rows || ' rows inserted');
  dbms_sql.close_cursor(c);
end;
/
select * from gate_log;

Output:

3 rows inserted

PL/SQL procedure successfully completed.

   FLIGHT_ID GATE
____________ _______
        2800 A1
        2801 B7
        2802 C3

Both placeholders are bound to collections of three elements, so one EXECUTE inserts three rows and returns 3. The example drops the table afterward.

Bind Part of a Collection

The optional lower and upper index bind only a range of elements.

Example:

declare
  c       integer := dbms_sql.open_cursor;
  v_ids   dbms_sql.number_table;
  v_gates dbms_sql.varchar2_table;
  v_rows  integer;
begin
  for i in 1 .. 5 loop
    v_ids(i) := 2900 + i;  v_gates(i) := 'D' || i;
  end loop;
  dbms_sql.parse(c, 'insert into gate_log values (:id, :gate)', dbms_sql.native);
  dbms_sql.bind_array(c, 'id', v_ids, 2, 4);               -- only elements 2 to 4
  dbms_sql.bind_array(c, 'gate', v_gates, 2, 4);
  v_rows := dbms_sql.execute(c);
  dbms_output.put_line(v_rows || ' rows inserted');
  dbms_sql.close_cursor(c);
end;
/
select * from gate_log order by flight_id;

Output:

3 rows inserted

PL/SQL procedure successfully completed.

   FLIGHT_ID GATE
____________ _______
        2902 D2
        2903 D3
        2904 D4

Of five elements, only 2 to 4 are inserted. The example drops the table afterward.

Things to Know

• When the arrays have different sizes, only as many rows as the smallest array holds are processed: three IDs with two gates insert two rows, without an error.
• Sparse collections work: missing indexes are skipped.
• If the statement can be written at compile time, EXECUTE IMMEDIATE inside FORALL is simpler.

Related Guides

• How to Use FORALL for Bulk DML in PL/SQL
• How to Describe a Query at Run Time with DBMS_SQL.DESCRIBE_COLUMNS

Conclusion

DBMS_SQL.BIND_ARRAY binds a collection to a placeholder so one execution processes every element. Use it for bulk DML in statements built at run time, and bind a range of elements when only part of a collection should be used.