The DEFINE_ARRAY stored procedure defines the collection into which the row values are fetched, with a FETCH_ROWS function call, for a given column.
The DEFINE_ARRAY stored procedure can use a single SELECT statement to retrieve multiple rows and pass them to COLUMN_VALUE as parameters.
Scalar and LOB types of the collection
You can declare local variables as the following types and use DBMS_SQL to fetch any number of rows into them. These types can be the same as those specified by the BIND_ARRAY stored procedure.
TYPE binary_double_table
IS TABLE OF BINARY_DOUBLE INDEX BY BINARY_INTEGER;
TYPE binary_float_table
IS TABLE OF BINARY_FLOAT INDEX BY BINARY_INTEGER;
TYPE bfile_table IS TABLE OF BFILE INDEX BY BINARY_INTEGER;
TYPE blob_table IS TABLE OF BLOB INDEX BY BINARY_INTEGER;
TYPE clob_table IS TABLE OF CLOB INDEX BY BINARY_INTEGER;
TYPE date_table IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE interval_day_to_second_Table
IS TABLE OF dsinterval_unconstrained
INDEX BY BINARY_INTEGER;
TYPE interval_year_to_MONTH_Table
IS TABLE OF yminterval_unconstrained
INDEX BY BINARY_INTEGER;
TYPE number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE time_table IS TABLE OF time_unconstrained
INDEX BY BINARY_INTEGER;
TYPE time_with_time_zone_table
IS TABLE OF time_tz_unconstrained
INDEX BY BINARY_INTEGER;
TYPE timestamp_table
IS TABLE OF timestamp_unconstrained
INDEX BY BINARY_INTEGER;
TYPE timestamp_with_ltz_Table
IS TABLE OF timestamp_ltz_unconstrained
INDEX BY BINARY_INTEGER;
TYPE timestamp_with_time_zone_Table
IS TABLE OF timestamp_tz_unconstrained
INDEX BY BINARY_INTEGER;
TYPE urowid_table IS TABLE OF UROWID INDEX BY BINARY_INTEGER;
TYPE varchar2_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
Syntax
DBMS_SQL.DEFINE_ARRAY (
c IN INTEGER,
position IN INTEGER,
<table_variable> IN <datatype>
cnt IN INTEGER,
lower_bnd IN INTEGER);
<table_variable> and the corresponding <datatype> can be combined into any of the following matching pairs, and DEFINE_ARRAY is reloaded to accept different data types:
<n_tab> Number_Table
<c_tab> Varchar2_Table
<d_tab> Date_Table
<bl_tab> Blob_Table
<cl_tab> Clob_Table
Parameters
| Parameter | Description |
|---|---|
| c | The ID of the cursor to be bound to the array. |
| position | The relative position of the column in the defined array. This value starts at 1. |
| table_variable | The local variable that has been declared as <datatype>. |
| cnt | The number of rows to be fetched. |
| lower_bnd | The lower-bound index, starting from which the results are copied to the collection. |
Usage notes
The value of
(cnt)must be an integer greater than 0; otherwise, an exception will be thrown.The value of
lower_bndcan be a positive number, a negative number, or 0.Queries that call
DEFINE_ARRAYcannot contain array bindings.