NativeOracleTable Class

Represents Oracle Nested table type.

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

Inheritance Hierarchy

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

Syntax

[Visual Basic]
Public Class NativeOracleTable 
   Inherits NativeOracleArray
   Implements IOracleArray, IOracleObjectBase, ICollection, IEnumerable, IList, IDisposable

[C#]
public class NativeOracleTable : NativeOracleArray, IOracleArray, IOracleObjectBase, ICollection, IEnumerable, IList, IDisposable

Remarks

The behavior of the NativeOracleTable is similar to NativeOracleArray class. The main difference is that NativeOracleTable can contain unlimited number of the elements.

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

Note that NativeOracleTable 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 NativeOracleTable 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 
  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'));

[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()) {  
        NativeOracleTable oraTable = dataReader.GetNativeOracleTable(index);  
         if (!oraTable.IsNull) {  
            string[] employees = new string[oraTable.Count];  
            for (int i = 0; i < oraTable.Count; i++)  
                employees[i] = (string)oraTable[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();  
}  
Console.ReadLine();

[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 oraTable As NativeOracleTable = dataReader.GetNativeOracleTable(index)
         If Not oraTable.IsNull Then
             Dim employees(oraTable.Count) As String
             Dim I As Integer
             For I = 0 To oraTable.Count - 1
                 employees(I) = oraTable(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