dotConnect for Oracle Documentation
Devart.Data.Oracle Namespace / NativeOracleArray Class
Members Example

In This Topic
    NativeOracleArray Class
    In This Topic
    Represents an Oracle collection (VARRAY).
    Syntax
    Remarks

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

    This class represents OCI arrays, and is intended for use in connected data access model.

    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 Devart.Data.Oracle.NativeOracleArray.Capacity specified at Oracle declaration of the VARRAY.

    Indexes in this collection are zero-based.

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

    This class is not available in OracleConnection.Direct mode.

    Example

    This example demonstrates how to retrieve a NativeOracleArray object from the 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'));
    
    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()) { 
                    NativeOracleArray oraArray = dataReader.GetNativeOracleArray(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(); 
    }
    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 NativeOracleArray = dataReader.GetNativeOracleArray(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
    Inheritance Hierarchy

    System.Object
       System.MarshalByRefObject
          Devart.Data.Oracle.NativeOracleObjectBase
             Devart.Data.Oracle.NativeOracleArray
                Devart.Data.Oracle.NativeOracleTable

    Requirements

    Target Platforms: Windows 7, Windows Vista SP1 or later, Windows XP SP3, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2

    See Also