Represents a client-side cached Oracle Nested table type.
This example demonstrates how to retrieve OracleTable 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
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'));
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()) {
OracleTable oraTable = dataReader.GetOracleTable(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();
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 OracleTable = dataReader.GetOracleTable(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
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