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

OracleArrayDataReader Class
Allows to read a forward-only stream of rows from an OracleArray or OracleTable.
Syntax
Remarks

As OracleArray and OracleTable have structure similar to usual database tables, it is natural to work with them in a similar way. For this purpose, OracleArrayDataReader implements the functionality of OracleDataReader for OracleArray and OracleTable.

To create a new instance of OracleArrayDataReader, the OracleDataReader.GetValue method of the OracleDataReader object should be used instead of calling a constructor directly.

This class is not available in OracleConnection.Direct mode.

Example

The sample demonstrates how OracleArrayDataReader may be retrieved and used. To create required objects and fill the table with data the following script should be executed:

CREATE OR REPLACE TYPE TEmployees AS 
  TABLE OF VARCHAR2(40);

CREATE TABLE CompanyEmpObject (
  Code NUMBER PRIMARY KEY,
  Company VARCHAR2(40),
  Employees TEmployees        
)NESTED TABLE Employees STORE AS N_TAB;

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'));
// Open a connection
OracleConnection connection = new OracleConnection("User Id=scott;Password=tiger;Data Source=ora1110");
connection.Open();

// Execute a simple command reading all records from CompanyEmpObject
OracleCommand command = new OracleCommand("SELECT * FROM CompanyEmpObject", connection);
OracleDataReader dataReader = command.ExecuteReader();

try
{
        // Get index of the Employees column
    int index = dataReader.GetOrdinal("Employees"); 
    while (dataReader.Read())
    {                    
        Console.WriteLine(dataReader["Company"] + ":");

        // The GetValue method returns OracleArrayDataReader, which 
        // allows to read records inside the Employees nested table.
        OracleArrayDataReader oraTableReader = (OracleArrayDataReader)dataReader.GetValue(index);
        // The OracleArrayDataReader may be used in the same way as usual data reader:
        while (oraTableReader.Read())
            Console.Write(oraTableReader.GetString(0) + "|");

        Console.WriteLine("\n" + new string('*', 79));
        }
}
catch (OracleException ex)
{
        Console.WriteLine(ex.Message);
}
finally
{
        dataReader.Close();
        connection.Close();
}
Console.ReadLine();
'Open a connection
Dim connection = New OracleConnection("User Id=scott;Password=tiger;Data Source=ora1110")
connection.Open()

' Execute a simple command reading all records from CompanyEmpObject
Dim command = New OracleCommand("SELECT * FROM CompanyEmpObject", connection)
Dim dataReader As OracleDataReader = command.ExecuteReader()

Try
        ' Get index of the Employees column
Dim index As Integer = dataReader.GetOrdinal("Employees")

        While dataReader.Read()
                Console.WriteLine(dataReader.Item("Company") + ":")
                ' The GetValue method returns OracleArrayDataReader, which 
                ' allows to read records inside the Employees nested table.
        Dim oraTableReader As OracleArrayDataReader = dataReader.GetValue(index)
                ' The OracleArrayDataReader may be used in the same way as usual data reader:                    
                While (oraTableReader.Read())
                        Console.Write(oraTableReader.GetString(0) + "|")
                End While
                Console.WriteLine(vbCrLf + New String("*", 79))
        End While
Catch ex As OracleException
        Console.WriteLine(ex.Message)
Finally
        dataReader.Close()
        connection.Close()
End Try
Console.ReadLine()
Inheritance Hierarchy

System.Object
   System.MarshalByRefObject
      Devart.Data.Oracle.OracleArrayDataReader

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