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 🙂

Working with Oracle Object Types part 1

I have been working with Oracle db for the past few days and found out that I can store objects in a table. Basically I can create a table with only one column of type ‘TYPES’ but I wouldn’t go to that extent :).

Here is an example on creating a type

CREATE OR REPLACE
TYPE MYSCHEMA.USER_TYPE AS OBJECT
(
	ID INTEGER,
        FIRSTNAME VARCHAR2(20),
        SURNAME VARCHAR2(20)
);

We can also create a collection of Types based on existing Objects

CREATE OR REPLACE TYPE MYSCHEMA.USER_TAB  AS TABLE OF  USER_TYPE;

It’s getting more interesting when I know that I can pass the object or the collection of objects to a procedure just like a normal parameter. this means that I don’t have to do a loop in .Net side when dealing with a collection of objects.

FUNCTION GetUsers(
    userId IN User.ID%TYPE
) RETURN USER_TAB
AS
  returnTab   USER_TAB;
BEGIN
    SELECT   USER_TYPE(
                ID,
                FIRSTNAME,
                SURNAME)
        BULK COLLECT
        INTO returnTab
        FROM
            USER
        WHERE
            ID = userId;

    RETURN returnTab;
END; 

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);