How to call Oracle stored procedure with %rowtype in/out parameter directly from code

Recently I have done a lot of development work against Oracle databases using ODP.NET and PL/SQL. Coming from a SQL Server background, this took some getting used to. There are a lot of similarities but there are a lot more differences between the two products. An ANSI standard only takes you so far...

I was triggered to write this post by this StackOverflow question. The OP wanted to call a stored procedure that accepted a parameter with the following signature:

io_user in out user%rowtype

and I thought: how hard can it be? Turns out: pretty hard. ODP.NET does not natively support %ROWTYPE parameters so it involves some trickery.

Let's start with an example Oracle stored procedure that illustrates the problem:

create or replace procedure sproc_test(bvo_param in out bvo%rowtype) is
begin
    select * into bvo_param
    from bvo
    where bvo.id = bvo_param.id;
end

The exact type of the parameter is irrelevant in this example: there exists a table called BVO in the current schema and it has an ID column.

Since ODP.NET does not know the concept of %ROWTYPE parameters, we must resort to other means to call this stored procedure. We can use the following code to call this procedure and get results:

using (var cmd = connection.CreateCommand())
{
    cmd.CommandText = "declare" +
                      "    bvo_param bvo%rowtype;" +
                      "begin" +
                      "    bvo_param.id := :id;" +
                      "    prc_test_rw(bvo_param => bvo_param);" +

                      "    open :cursor for select bvo_param.name name" +
                      "                     from dual;" +
                      "end;";
    cmd.CommandType = CommandType.Text;

    // Input and output parameters.
    var idParam = new OracleParameter(
        "id", OracleDbType.Decimal, ParameterDirection.Input) { Value = 99 };
    var cursorParam = new OracleParameter(
        "cursor", OracleDbType.RefCursor, ParameterDirection.Output);
    cmd.Parameters.AddRange(new[] { idParam, cursorParam });

    // Execute the command.
    cmd.ExecuteNonQuery();

    // Retrieve cursor results.
    var cursorValue = (OracleRefCursor) cursorParam.Value;
    using (var dataReader = cursorValue.GetDataReader())
    {
        if (dataReader.Read())
        {
            var name = dataReader["name"];
            Console.WriteLine(name);
        }
    }
}

Let's break this down by starting with the actual command that gets sent to the database. It declares a variable bvo_param of the same type that is accepted by the stored procedure. You use this variable to bind actual property values to parameters. In the example, bvo_param.id is bound to idParam. Using the variable, you can now call the stored procedure. This is enough for the input side of things.

If you also want to see output, the best way is to use a cursor as output parameter. You simply select all the necessary values from bvo_param, in this case we're only interested in the name. In your code you simply read from this cursor to get the results.

So ... although a lot harder than I think it should be, this is still a reasonably simple way to call Oracle stored procedures (or functions) that have %ROWTYPE parameter(s).

P.S.: There is a second way of getting output, although not as clean as the one described. You could use dbms_output.get_line to get a string value that encodes the desired output. Check here for more details.