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 🙂

Retrieving a list from Oracle function

I have spent a few hours trying to figure out how to get the return value from an oracle function and the only thing that I got was something like ” <function name> is not a procedure or defined “.

The problem was not from my function, to retrieve the returned cursor from the function we have to insert an output parameter as the first parameter.

OracleParameter refCursor = new OracleParameter();

refCursor.OracleDbType = OracleDbType.RefCursor;

refCursor.Direction = ParameterDirection.ReturnValue;

oracleCommand.Parameters.Insert(0, refCursor);