The DEFINE_ARRAY stored procedure defines a collection of columns to extract rows from (using FETCH_ROWS).
DEFINE_ARRAY can retrieve rows in bulk using a single SELECT statement and pass them as parameters to COLUMN_VALUE.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Scalar and LOB types of collections
Local variables can be declared as the following types and then used with DBMS_SQL to extract 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);
In the syntax, <table_variable> and its corresponding <datatype> can be any of the following pairs. DEFINE_ARRAY is overloaded 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 | Explanation |
|---|---|
| c | The cursor ID to bind the array. |
| position | The relative position of the column in the defined array. The first column in the statement has a position of 1. |
| table_variable | A local variable declared as <datatype>. |
| cnt | The number of rows to extract. |
| lower_bnd | The lower bound index from which to start copying results into the collection. |
Considerations
The count
(cnt)must be a positive integer. Otherwise, an exception is raised.lower_bndcan be a positive number, a negative number, or zero.Queries that call
DEFINE_ARRAYcannot contain array bindings.