BIND_ARRAY v16

Suggest edits

The BIND_ARRAY procedure binds a value or set of values to a variable in a cursor, based on the name of the variable.

BIND_ARRAY (<c> IN NUMBER, <name> IN VARCHAR2, 
  <table_variable> IN { FLOAT_TABLE | BLOB_TABLE | CLOB_TABLE | VARCHAR2_TABLE | NUMBER_TABLE | TIMESTAMP_TABLE |
  INTEGER_TABLE | ROWID_TABLE | TEXT_TABLE } [,<index1> IN NUMBER, <index2> IN NUMBER]);

Parameters

c

Cursor ID of the cursor for the SQL command with the values to be bound.

name

Name of the collection. The name must be less than or equal to 30 bytes.

table_variable

A local variable that is declared to be one of the following datatypes:

DatatypeDescription
<bflt_tab>FLOAT_TABLE
<bl_tab>BLOB_TABLE
<cl_tab>CLOB_TABLE
<c_tab>VARCHAR2_TABLE
<n_tab>NUMBER_TABLE
<tms_tab>TIMESTAMP_TABLE
<int_tab>INTEGER_TABLE
<rid_tab>ROWID_TABLE
<txt_tab>TEXT_TABLE

index1

Index for the table element that marks the lower bound of the range if you are binding a range. This value must be less than or equal to the value of index2. All elements between index1 and index2 are used in the bind. This is an optional parameter that can be used with three parameters or five parameters but not four.

index2

Index for the table element that marks the upper bound of the range. This is an optional parameter that can be used with three parameters or five parameters but not four.

Notes

If you don't specify indexes in the bind call, and two different binds in a statement specify tables that contain a different number of elements, then the number of elements actually used is the minimum number between all tables. This is also the case if you specify indexes. The minimum range is selected between the two indexes for all tables.

Not all bind variables in a query must be array binds. Some can be regular binds, and the same value is used for each element of the collections in expression evaluations and so forth.

Example

CREATE TABLE tab_test (col_int INTEGER, col_charactervarying VARCHAR(40));

-- BIND_ARRAY (used for multiple insert/update/delete in single command
DECLARE
   c            INTEGER;
   dbms_return  INTEGER;
   res          NUMBER;
   fetch_ret    INTEGER;
   varint       DBMS_SQL.INTEGER_TABLE;
BEGIN
   c := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE (c,'INSERT into tab_test(col_int, col_charactervarying) values ( :bvarint, :bvarname )',
            DBMS_SQL.NATIVE);
   varint(1) := 10;
   varint(2) := 11;
   varint(4) := 12;
   DBMS_SQL.BIND_ARRAY (c, 'bvarint', varint);
   DBMS_SQL.BIND_VARIABLE (c, 'bvarname', 'edb_user');
   dbms_return := DBMS_SQL.EXECUTE(c);
   DBMS_OUTPUT.PUT_LINE(' RETURN VALUE ...' || dbms_return );
   COMMIT;
   DBMS_SQL.CLOSE_CURSOR (c);
END;
/

-- this inserts three rows using bind_array so verify them in the table.
SELECT * FROM tab_test;
 col_int | col_charactervarying 
---------+----------------------
      10 | edb_user
      11 | edb_user
      12 | edb_user
-- For col_int we are passing bind_array with the size of 3 elements, and as col_charactervarying we are 
-- passing bind_variable. So for all the insertions, we are using same bind_variable (edb_user).

(3 rows)
← Prev

DBMS_SQL

Next →

BIND_VARIABLE