Why Oracle Sucks Today – the return parameter must be the first parameter

For the last year I had abandoned Oracle’s ODP.NET driver in lieu of Microsoft’s ADO.NET driver, for the simple reason that calls to PL/SQL functions would never return anything but “0”.  I’ve been revisiting this issue, because 1) Microsoft’s Oracle ADO.NET support is being deprecated and 2) I need to get arrays (and possibly tables) working as parameters and return values, which is something that Microsoft’s driver doesn’t support but supposedly ODP.NET does.

So, I started off with a simple test,  why does the following fail (but works with Microsoft’s driver):

PL/SQL function:

FUNCTION get_int (i_something IN NUMBER)
RETURN NUMBER
IS
BEGIN
return i_something + 1;
END;

C# code:

public static void CallFunctionReturningInt(OracleConnection conn)
{
OracleTransaction trans = conn.BeginTransaction();
OracleCommand cmd = conn.CreateCommand();
cmd.Transaction = trans;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = “TC_CODE.RECSET_CONVERTER.get_int”;

OracleParameter dbParam = new OracleParameter();
dbParam.ParameterName = “i_something”;
dbParam.OracleType = OracleType.Int32;
dbParam.Value = 1;
dbParam.Direction = ParameterDirection.Input;
cmd.Parameters.Add(dbParam);

dbParam = new OracleParameter();
dbParam.Direction = ParameterDirection.ReturnValue;
dbParam.Size = 4;
dbParam.OracleType = OracleType.Int32;
cmd.Parameters.Add(dbParam);

object ret = cmd.ExecuteNonQuery();

trans.Commit();

Console.WriteLine(cmd.Parameters[1].Value.ToString());
}

After much googling for why this fails with ODP.NET, I stumbled, quite by accident, on this gem (https://forums.oracle.com/forums/thread.jspa?threadID=387950):

For some reason only knows to Larre, your returnvalue must be the FIRST parameter.

(I think “Larre” refers to Larry Ellison, haha).  And a slightly more descriptive reason:

Just fyi, here’s the reason the returnvalue needs to be first. ODP constructs an anonymous block to execute the stored procedure, along the lines of the following:

begin :retval := myproc(:paramval);end;

and the default for ODP is to bind by position, rather than by name. So the fist parameter added needs to be the return value.

This is an absurd constraint, in my opinion, but it does work.

Advertisements

One thought on “Why Oracle Sucks Today – the return parameter must be the first parameter

  1. There’s also an OracleCommand property called “BindByName”. If you set that to true, then it will bind the parameters using the names instead of by order. Ran across it trying to correct a nasty bug.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s