Working with VARRAY Type

dotConnect for Oracle provides two ways of working with Arrays and Nested tables:

• fill dataset, in this case array data will be stored at the single table of dataset and a relation set to the table that associates a row from base selection with array rows.
• get an object of OracleArray type and access its items through indexed property.

If such types are created:

CREATE TYPE TArray1 AS VARRAY (5) OF NUMBER;

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

CREATE TABLE ArrayTable (
  Code NUMBER,
  Title VARCHAR2(10),
  Arr1 TArray1,
  Arr2 TArray2,
);

Filled dataset for the query

SELECT * FROM ArrayTable

will have three tables at dataset: ArrayTable, ArrayTableArr1, ArrayTableArr2. Using this way has a restriction that you are unable to post updated data to the server by DataAdapter.Update method.

The another way to access data from Arrays and Nested tables is to use OracleArray and OracleTable classes. For example, for mentioned above types you can write the next code:

OracleArray oraArray = dataReader.GetOracleArray(i);
foreach (object obj in oraArray) {
  lbResult.Items.Add(obj.ToString());
}

dim oraArray as OracleArray = dataReader.GetOracleArray(i);
dim obj as object
for each obj in oraArray
  lbResult.Items.Add(obj.ToString())
next

You can use VARRAY type for parameters of SQL and PL/SQL statements. To do it you should set Array type to OracleParameter.DataType.

For example:

OracleCommand cmd = OracleConnection1.CreateCommand();
cmd.CommandText = "INSERT INTO ArrayTable VALUES (:CODE, :TITLE, : ARR1, :ARR2)";
...
OracleArray arr1 = new OracleArray("SCOTT.TARRAY1", OracleConnection1);
arr1.Add(10);
arr1.Add(20);
arr1.Add(30);
...
cmd.Parameters["ARR1"].DbType = OracleDbType.Array;
cmd.Parameters["ARR1"].Value = arr1;
...
cmd.ExecuteNonQuery();

dim cmd as OracleCommand = OracleConnection1.CreateCommand()
cmd.CommandText = "INSERT INTO ArrayTable VALUES (:CODE, :TITLE, : ARR1, :ARR2)"
...
dim arr1 as OracleArray = new OracleArray("SCOTT.TARRAY1", OracleConnection1)
arr1.Add(10)
arr1.Add(20)
arr1.Add(30)
...
cmd.Parameters("ARR1").DbType = OracleDbType.Array
cmd.Parameters("ARR1").Value = arr1
...
cmd.ExecuteNonQuery()