OracleArray Class

Represents a client-side cached Oracle collection (VARRAY).

For a list of all members of this type, see OracleArray members.

Inheritance Hierarchy

System.Object
Devart.Data.Oracle.OracleArray
Devart.Data.Oracle.OracleTable

Syntax

[Visual Basic]
Public Class OracleArray 
   Implements ICustomOracleArray, IOracleArray, IOracleObjectBase, ICollection, IEnumerable, IList, IListSource, INullable, ICloneable

[C#]
public class OracleArray : ICustomOracleArray, IOracleArray, IOracleObjectBase, ICollection, IEnumerable, IList, IListSource, INullable, ICloneable

Remarks

The OracleArray class provides methods to access elements of the collection. Items values are accessible by Item indexed property.

The array elements can be added to the collection dynamically by calling Add, Insert methods or removed by calling Remove or RemoveAt but the maximum number of the elements cannot exceed Capacity specified at Oracle declaration of the VARRAY.

Indexes in this collection are zero-based.

Note that OracleArray can be used in data binding with DataGrid as it implements IListSource interface.

Example

This example demonstrates how to retrieve OracleArray object from server and display its elements. To create required type and table and fill it with data you can use the following script:

CREATE OR REPLACE TYPE TEmployees AS 
  VARYING ARRAY(10000) OF VARCHAR2(40);

CREATE TABLE CompanyEmpObject (
  Code NUMBER PRIMARY KEY,
  Company VARCHAR2(40),
  Employees TEmployees	
);
INSERT INTO CompanyEmpObject
  (Code, Company, Employees)
VALUES
  (1, 'Microsoft', TEmployees('PROGRAMMER', 'MANAGER', 'ELECTRICIAN', 'CLEANUP MEN'));
INSERT INTO CompanyEmpObject
  (Code, Company, Employees)
VALUES
  (2, 'Borland', TEmployees('PROGRAMMER', 'ELECTRICIAN', 'COURIER'));
INSERT INTO CompanyEmpObject
  (Code, Company, Employees)
VALUES
  (3, 'MC Donalds', TEmployees('SHOP ASSISTANT', 'CLERK',  'CLEANUP MEN'));
INSERT INTO CompanyEmpObject
  (Code, Company, Employees)
VALUES
  (4, 'Ford', TEmployees('MECHANICIAN', 'MANAGER'));

[C#]

OracleConnection connection = new OracleConnection("User Id=scott;Password=tiger;Data Source=ora"); 
OracleCommand command = new OracleCommand("SELECT * FROM CompanyEmpObject", connection); 
connection.Open(); 
OracleDataReader dataReader = command.ExecuteReader(); 
try{ 
  int index = dataReader.GetOrdinal("Employees"); 
  while (dataReader.Read()) { 
    OracleArray oraArray = dataReader.GetOracleArray(index); 
    if (!oraArray.IsNull) { 
      string[] employees = new string[oraArray.Count]; 
      for (int i = 0; i < oraArray.Count; i++) 
        employees[i] = (string)oraArray[i]; 
 
      Console.WriteLine("Company: " + dataReader.GetString(1)); 
      foreach (string str in employees) 
        Console.WriteLine("\t"+str); 
    } 
    else{ 
      Console.WriteLine("Company: " + dataReader.GetString(1)); 
      Console.WriteLine("No vacancies"); 
    } 
  } 
} 
catch (Devart.Data.Oracle.OracleException ex) { 
  Console.WriteLine(ex.Message); 
} 
finally{ 
  dataReader.Close(); 
  connection.Close(); 
}

[Visual Basic]

Dim connection As New OracleConnection("User Id=scott;Password=tiger;Data Source=ora")
 Dim command As New OracleCommand("SELECT * FROM CompanyEmpObject", connection)
 connection.Open()
 Dim dataReader As OracleDataReader = command.ExecuteReader()
 Try
   Dim index As Integer = dataReader.GetOrdinal("Employees")
   While dataReader.Read()
     Dim oraArray As OracleArray = dataReader.GetOracleArray(index)
     If Not oraArray.IsNull Then
       Dim employees(oraArray.Count) As String
       Dim I As Integer
       For I = 0 To oraArray.Count - 1
         employees(I) = oraArray(I)
       Next I
       Console.WriteLine("Company: " & dataReader.GetString(1))
       For Each str As String In employees
         Console.WriteLine(Chr(9) & str)
       Next
     Else
       Console.WriteLine("Company: " & dataReader.GetString(1))
       Console.WriteLine("No vacancies")
     End If
   End While
 Catch ex As Devart.Data.Oracle.OracleException
   Console.WriteLine(ex.Message)
 Finally
   dataReader.Close()
   connection.Close()
 End Try

See Also

Language