VARRAY Data Type

Everything considered in Working with objects is right for Arrays. Some problems appear when you need to use large arrays in dataset. As ODAC creates one field for each array item great number of TField objects are created. As a result the perfomance decreases. So ODAC has the limitation and creates fields for first 1000 items. However you can access all items with TOraArray object. Another way is to set TOraQuery.SparseArray to True and access array items by TArrayField object.

If such types are created

CREATE TYPE TODACArray1 AS VARRAY (5) OF NUMBER;

CREATE TYPE TODACArray2 AS VARRAY (4) OF CHAR(10);

CREATE TABLE ODAC_Array (
  Code NUMBER,
  Title VARCHAR2(10),
  Arr1 TODACArray1,
  Arr2 TODACArray2,
);

To access array items you can call FieldByName method. For example

Value := Query.FieldByName('Arr1[0]').AsInteger;

If ObjectField property is True this code is correct

Value := TArrayField(Query.FieldByName('Arr1')).Fields[0].AsInteger;

Using TOraDataSet.GetArray you can access array items through TOraArray object

Value:= Query.GetArray('Arr1').ItemAsInteger[0];

You can use VARRAY type for parameters of SQL and PL/SQL statements. You need to assign dtArray to TOraParam.DataType and use TOraParam.AsArray property to access array items.

For example:

var
  OraSQL: TOraSQL;
. . .
OraSQL.SQL.Text := 'INSERT INTO ODAC_Array (Code, Arr1, Arr2)' +
                   'VALUES (:Code, :Arr1, :Arr2)';

OraSQL.ParamByName('Code').AsInteger := 10;

with OraSQL.ParamByName('Arr1').AsArray do begin
  AllocObject(OraSession.OCISvcCtx, 'TODACArray1');
  ItemAsInteger[0] := 12;
  AttrAsInteger['[1]'] := 10;
  ItemAsInteger[3] := 133;
end;

with OraSQL. ParamByName('Arr2').AsArray do begin
  OCISvcCtx:= OraSession.OCISvcCtx;
  AllocObject('TODACArray2');
  AttrAsString['[2]']:= 'eeee';
  ItemAsString[0]:= 'FFFFF';
end;

OraSQL.Execute;

VARRAY Data Type

See Also