The Oracle Object usage is considered in this article. We will consider object creation, attribute manipulating, and about object inserting and retrieving in given article.

• Overview
• Creating Oracle Objects and Manipulating Attributes
• Inserting and Retrieving Objects

Overview

dotConnect for Oracle allows you to work with Oracle object types, including array and table types. This includes manipulating object attributes and performing CRUD (create read update delete) operations on rows with object type fields.

Object types can be used with either typed or untyped OracleObjects. Typed OracleObject is a .NET class representing a single user-defined type (UDT); instances of this UDT can be accessed in the same way as usual .NET objects. Such kind of OracleObjects can be generated using the Oracle Object Wizard, see the Using Object Wizard for Working with Oracle Objects topic for more information about this.

This topic describes how to use untyped OracleObjects which are instances of the OracleObject class. Specific information about arrays and tables is contained in the topics VARRAY Type and PL/SQL Tables.

We will work with the following user-defined types and table:

CREATE TYPE TAddress AS OBJECT (
  Country VARCHAR2(30),
  City VARCHAR2(30),
  Street VARCHAR2(30),
  Apartment NUMBER
);

CREATE TYPE TPerson AS OBJECT (
  Name VARCHAR2(30),
  Address TAddress,
  Phone VARCHAR2(20),
  BirthDate DATE
);

CREATE TABLE EmpObject (
  Person TPerson,
  Job VARCHAR2(9),
  HireDate DATE,
  Sal NUMBER(7,2)
);

Creating Oracle Objects and Manipulating Attributes

The needed object type should be specified for OracleObject instance creation. Apparently, an opened connection (say, oracleConnection1) is needed at this moment so that it is possible to collect the information about the attributes of this UDT. For example, an instance of the TAddress type can be created as

OracleObject address = new OracleObject("TADDRESS", oracleConnection1);

dim address As OracleObject = new OracleObject("TADDRESS", oracleConnection1)

Another way of creating object is to use the OracleType class, which instances represent UDTs themselves:

OracleType addressType = OracleType.GetObjectType("TADDRESS", oracleConnection1);
OracleObject address1 = new OracleObject(addressType);
OracleObject address2 = new OracleObject(addressType);

Dim AddressType As OracleType = OracleType.GetObjectType("TADDRESS", oracleConnection1)
Dim address1 As OracleObject = New OracleObject(addressType)
Dim address2 As OracleObject = New OracleObject(addressType)

After OracleObject is created, its fields can be accessed in the following way:

address["Country"] = "USA";
address["City"] = "New York, NY";
address["Street"] = "450 Seventh Avenue, Suite 2102";

address("Country") = "USA"
address("City") = "New York, NY"
address("Street") = "450 Seventh Avenue, Suite 2102"

Also you can use the elements of the Attributes collection of the corresponding OracleType instance:

OracleAttribute country = addressType.Attributes["Country"];
address[country] = "USA";

Dim country As OracleAttribute = addressType.Attributes("Country")
address(country) = "USA"

The main difference between typed and untyped OracleObjects is the fact that the fields of typed OracleObjects may be accessed through the properties of the corresponding classes generated by the Oracle Objects Wizard.

Inserting and Retrieving Objects

The simplest way of inserting a UDT instance to the table is passing it as a command parameter. Note that the type of the corresponding parameter should be set to OracleDbType.Object:

OracleCommand cmd = oracleConnection1.CreateCommand();
cmd.CommandText = "INSERT INTO EmpObject (CODE, PERSON, JOB, HIREDATE, SAL) VALUES (:CODE, :PERSON, :JOB, : HIREDATE, :SAL)";

cmd.Parameters.Add(new OracleParameter("CODE", 10));
cmd.Parameters.Add(new OracleParameter("PERSON", OracleDbType.Object));
cmd.Parameters.Add(new OracleParameter("JOB", "MANAGER"));
cmd.Parameters.Add(new OracleParameter("HIREDATE", new DateTime(1990, 1, 1)));
cmd.Parameters.Add(new OracleParameter("SAL", 1000));

OracleObject address = new OracleObject("TADDRESS", oracleConnection1);
address["CITY"] = "NEW YORK";
address["STREET"] = "450 Seventh Avenue, Suite 2102";
OracleObject person = new OracleObject("TPERSON", oracleConnection1);
person["ADDRESS"] = address;
person["NAME"] = "John Smith";

cmd.Parameters["PERSON"].Value = person;
...

cmd.ExecuteNonQuery();

dim cmd as OracleCommand = oracleConnection1.CreateCommand()
cmd.CommandText = "INSERT INTO EmpObject & _
    (CODE, PERSON, JOB, HIREDATE, SAL) VALUES (:CODE, :PERSON, :JOB, : HIREDATE, :SAL)"

cmd.Parameters.Add(new OracleParameter("CODE", 10))
cmd.Parameters.Add(new OracleParameter("PERSON", OracleDbType.Object))
cmd.Parameters.Add(new OracleParameter("JOB", "MANAGER"))
cmd.Parameters.Add(new OracleParameter("HIREDATE", new DateTime(1990, 1, 1)))
cmd.Parameters.Add(new OracleParameter("SAL", 1000))

dim address As OracleObject = new OracleObject("TADDRESS", oracleConnection1)
address("CITY") = "NEW YORK"
address("STREET") = "450 Seventh Avenue, Suite 2102"
dim person As OracleObject = new OracleObject("TPERSON", oracleConnection1)
person("ADDRESS") = address
person("NAME") = "John Smith"

cmd.Parameters("PERSON").Value = person
...

cmd.ExecuteNonQuery()

OracleObjects can be retrieved from the database in the same way as common data types:

OracleCommand cmd = new OracleCommand("select * from EmpObject", oracleConnection1);
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
   OracleObject person = (OracleObject)reader["Person"];
   Console.WriteLine(person["NAME"].ToString());
}

Dim Cmd = New OracleCommand("select * from EmpObject", oracleConnection1)
Dim Reader = cmd.ExecuteReader()
While reader.Read
   Dim Person As OracleObject = CType(Reader("Person"), OracleObject)
   Console.WriteLine(Person("NAME").ToString)
End While

You can use the GetOracleObject method instead of explicitly casting reader content:

OracleObject person = reader.GetOracleObject(0);

Dim person As OracleObject = reader.GetOracleObject(0)

Besides retrieving UDT as a single object, it can be expanded into several fields of simple data types. To do so, you can use an OracleDataTable object with the SplitObjects property set to true. Notice that SplitObjects is ignored unless the ObjectView property is set to false. In this case, object attributes should be specified in the format [UDT field name].[UDT attribute name]:

OracleDataTable table = new OracleDataTable("select * from EmpObject", oracleConnection1);
table.ObjectView = false;
table.SplitObjects = true;
table.Fill();

foreach (DataRow row in table.Rows) {
   Console.WriteLine(row["Person.Name"] + ": " + row["Person.Address.Street"]);
}

Dim table As OracleDataTable = New OracleDataTable("select * from EmpObject", con)
table.ObjectView = False
table.SplitObjects = True
table.Fill()

For Each row As DataRow In table.Rows
   Console.WriteLine(row("Person.Name") + ": " + row("Person.Address.Street"))
Next

See Also

OracleObject Class  | Using Object Wizard for Working with Oracle Objects | PL/SQL Tables | VARRAY Type