Working with Oracle Object Types part 2

Following previous post about working with Types in Oracle, this post will focus on using the types from .Net.

The easiest way to map the Oracle Types is by using Oracle .Net Data Provider (ODP) to generate the class for you:
– Open Server Explorer (Tools – Server Explorer)
– Add new connection (right click Data Connections and choose add connection), set Data source to Oracle Database and Data provider to Oracle Data Provider for .Net
– Fill up some connection details and press Ok
– At this stage you should be able to see the mapping of every Oracle object in Server Explorer (well, maybe not everything)
– Go to User-Defined Types node and choose one of the Types
– Right click on the type, Generate Custom Class, follow the wizard and click Finish

From previous post, I got USER_TYPE and USER_TAB objects generated and to use those objects from .Net:

OracleConnection conn = NEW OracleConnection(connectionString);

IF (conn.State == ConnectionState.Closed)
{
conn.Open();
}

try
{
    OracleCommand oCmd = NEW OracleCommand();
    oCmd.CommandText = "USERPKG.GetUsers";
    oCmd.Connection = conn;
    oCmd.CommandType = CommandType.StoredProcedure;
    oCmd.BindByName = true;

    OracleParameter nameParam = oCmd.PARAMETERS.ADD("userId", OracleDbType.Int32);
    nameParam.Value = userId;
    nameParam.Direction = ParameterDirection.Input;

    OracleParameter listParam = NEW OracleParameter("USER_TAB", OracleDbType.OBJECT);
    listParam.Direction = ParameterDirection.ReturnValue;
    listParam.UdtTypeName = "USER_TAB";
    oCmd.PARAMETERS.INSERT(0, listParam);

    oCmd.ExecuteNonQuery();
    USER_TAB searchResult = (USER_TAB)oCmd.PARAMETERS["USER_TAB"].Value;
    RETURN searchResult;
}
finally
{
    IF (conn.State == ConnectionState.Open)
    {
        conn.Close();
    }
}

As we are using oracle type, the UdtTypeName needs to be declared. So far this is the easiest way that I can find to use Oracle data types from .Net 🙂