ODAC application Techniques (iv) using VARRAY data types

It is recommended that you use an array when working with objects. But there may be problems when you need to use a large array in a dataset. For example, when a large array of ODAC is created for a field, a large number of Tfield fields are also created, which can cause the program's performance to be greatly reduced. Therefore, the ODAC limit can only create 1000 members for a field. But either way, you can use the Toraarray object to access all the members. Of course, you can do it in other ways. For example, set Toraquery.sparsearray to True and access array members by Tarrayfield objects.

If the following 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 an array member, you need to invoke the method Fieldbyname. For example:

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

If ObjectField property is True this code is right

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

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

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

You can use the Varray type as a parameter in SQL and PL/SQL statements. You need to specify Dtarray to Toraparam.datatype and use the Toraparam.asarray property to access the array members.

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;