DEFINE_ARRAY v17

Suggest edits

The DEFINE_ARRAY procedure defines the collection for a column into which you want to fetch rows with a FETCH_ROWS call. You can use this procedure to fetch multiple rows from a single SELECT statement.

When you fetch rows, they are copied into DBMS_SQL buffers until you run a COLUMN_VALUE call, which copies the rows into the table that was passed as an argument to the COLUMN_VALUE call.

DEFINE_ARRAY(<c> IN NUMBER, <position> IN NUMBER, 
  <table_variable> IN { FLOAT_TABLE | BLOB_TABLE | CLOB_TABLE | VARCHAR2_TABLE | NUMBER_TABLE | TIMESTAMP_TABLE |
  INTEGER_TABLE | ROWID_TABLE | TEXT_TABLE }, <cnt> IN NUMBER, <lower_bnd> IN NUMBER);

Parameters

c

ID of the cursor to which you want to bind an array.

position

Relative position of the column in the array being defined. The first column is position 1.

table_variable

A datatype that can be any one of the following matching pairs:

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

cnt

Number of rows to fetch. Must be a positive integer.

lower_bnd

Results are copied into the collection, starting at this lower bound index.

Example

CREATE TABLE tab_test (col_int INTEGER, col_charactervarying VARCHAR(40));
INSERT into tab_test(col_int, col_charactervarying) values (10, 'user1'), (10, 'user2'), (10, 'user3'), (10, 'user4');
INSERT into tab_test(col_int, col_charactervarying) values (10, 'user1'), (10, 'user2'), (10, 'user3'), (10, 'user4');

-- DEFINE_ARRAY (used in SELECT to get multipile rows)
DECLARE
    names    DBMS_SQL.VARCHAR2_TABLE;
    dpt_id   DBMS_SQL.INTEGER_TABLE;
    c        NUMBER;
    r        NUMBER;
    id       NUMBER;
    sql_stmt VARCHAR2(32767) :=
        'SELECT col_int, col_charactervarying FROM tab_test WHERE col_int = :b1';
BEGIN
    c := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(c, sql_stmt, dbms_sql.native);
    id := 10;
    DBMS_SQL.BIND_VARIABLE(c, 'b1', id);
    DBMS_SQL.DEFINE_ARRAY(c, 1, dpt_id, 3, -3);
    DBMS_SQL.DEFINE_ARRAY(c, 2, names, 3, -3);
    r := DBMS_SQL.EXECUTE(c);
    LOOP
      r := DBMS_SQL.FETCH_ROWS(c);
      EXIT WHEN r = 0;
      DBMS_SQL.COLUMN_VALUE(c, 1, dpt_id);
      DBMS_SQL.COLUMN_VALUE(c, 2, names);
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(c);
    DBMS_OUTPUT.PUT_LINE('last index in table = ' || names.LAST);
    -- loop through the names and sals collections
    FOR i IN names.FIRST .. names.LAST  LOOP
      DBMS_OUTPUT.PUT_LINE(' name = ' ||names(i) || ' department_id = ' || dpt_id(i));
    END LOOP;
END;

psql:bind_array.sql:67: last index in table = 4
psql:bind_array.sql:67:  name = user1 department_id = 10
psql:bind_array.sql:67:  name = user2 department_id = 10
psql:bind_array.sql:67:  name = user3 department_id = 10
psql:bind_array.sql:67:  name = user4 department_id = 10
psql:bind_array.sql:67:  name = user1 department_id = 10
psql:bind_array.sql:67:  name = user2 department_id = 10
psql:bind_array.sql:67:  name = user3 department_id = 10
psql:bind_array.sql:67:  name = user4 department_id = 10

Here, count size is 3 so in one loop, we are printing 3 matching rows and total matching rows are 8.

so 3,3,2 (total 3 loops--one loop will fetch 3 rows at a time because count is 3), and if we don't use define_array, then loop will be 8.
← Prev

COLUMN_VALUE_ROWID

↑ Up

DBMS_SQL

Next →

DEFINE_COLUMN