ODP.NET Tip: Bind Variables, the BindByName Property, and PL/SQL Function Return Values

A question was posed as a follow-up to a previous post here:

http://oradim.blogspot.com/2007/08/odpnet-tip-bind-variables-and.html

The poster wanted to know if you have set BindByName to "true" for an OracleCommand object and the command text for that object calls a PL/SQL function (which of course has a return value but no name), what name should you give to the parameter object that will receive the return value? It's a good question since BindByName set to "true" implies that you've given names to your bind variables!

The short answer is: It doesn't matter as it will be ignored anyway.

As in The Highlander movies, with return values, there can be only one. You can set ParameterDirection to "ReturnValue" for multiple parameters, but it is an error to do so. Try it and see what errors are raised.

Here's a quick little demo showing that it does not matter what name you give to the parameter that you declare as the return value.

First create a simple PL/SQL function (so that there is a return value!) like this:

create or replace function subtract_numbers(p1 in number, p2 in number) return number as
begin
  return p1 - p2;
end;
/

I've elected to do a simple bit of subtraction and this is for a reason. Since in subtraction the order of the parameters matters, it is easy to show that the parameters are being handled in the correct order. As you'll see in the sample code below, I use the values "48" and "2" so it would be obvious if the ordering were reversed.

Now, here's some simple (and normal caveats about no error handling, etc. apply) bit of C# to illustrate the point:

using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

class Program
{
  static void Main(string[] args)
  {
    // adjust connect string to suit your environment
    string constr = "user id=hr;" +
                    "password=hr;" +
                    "data source=orademo;" +
                    "pooling=false;" +
                    "enlist=false";

    // create and open connection
    OracleConnection con = new OracleConnection(constr);
    con.Open();

    // create command object
    OracleCommand cmd = con.CreateCommand();
    cmd.CommandText = "subtract_numbers";
    cmd.CommandType = CommandType.StoredProcedure;

    // set bindbyname to true so parameters can be added
    // in any order and to illustrate what happens with a
    // return value
    cmd.BindByName = true;

    // the first input parameter to the pl/sql function
    OracleParameter p1 = new OracleParameter();
    p1.Direction = ParameterDirection.Input;
    p1.OracleDbType = OracleDbType.Int64;
    p1.ParameterName = "p1";
    p1.Value = 48;

    // the second input parameter to the pl/sql function
    OracleParameter p2 = new OracleParameter();
    p2.Direction = ParameterDirection.Input;
    p2.OracleDbType = OracleDbType.Int64;
    p2.ParameterName = "p2";
    p2.Value = 2;

    // the return parameter from the pl/sql function
    OracleParameter p3 = new OracleParameter();
    p3.Direction = ParameterDirection.ReturnValue;
    p3.OracleDbType = OracleDbType.Int64;
    p3.ParameterName = "ignored";

    // add the parameters in a "wrong" order since
    // bindbyname is true -- this is key
    cmd.Parameters.Add(p1);  // input #1
    cmd.Parameters.Add(p3);  // return value
    cmd.Parameters.Add(p2);  // input #2

    // execute the stored pl/sql code
    cmd.ExecuteNonQuery();

    // write the result to the console window
    Console.WriteLine("The difference of {0} and {1} is {2}",
      p1.Value, p2.Value, p3.Value);

    // prevent console window from closing when run from VS
    Console.WriteLine();
    Console.Write("ENTER to continue...");
    Console.ReadLine();
    // clean up
    p3.Dispose();
    p2.Dispose();
    p1.Dispose();
    cmd.Dispose();
    con.Dispose();
  }
}

Notice how the parameters are added to the parameter collection "out of order". This is OK since BindByName is set to "true". You can comment out the line that sets BindByName and see an exception raised if you want. Anyway, when I execute this code, I get the expected results as follows:

The difference of 48 and 2 is 46

ENTER to continue...

So, even though the return value parameter was added to the collection in the second position everything works as desired. Yes, but I gave it a name of "ignored" so how do we know that name is not really being used? Well there are two easy ways to find out. One, just don't give the parameter a name at all and everything should continue to execute just fine. It's easy to ignore a name that's not there! Or, two, if you are an inquisitive type, enable SQL tracing (search the web for 10046 trace -- you'll find a million hits) and see what ODP.NET actually sent to the database. Here's a snippet of a trace file from the above code (with the parameter name of "ignored" in place):

PARSING IN CURSOR #2 len=54 dep=0 uid=82 oct=47 lid=82 tim=10601980885 hv=1771638930 ad='7ff39775518' sqlid='gx0kvypntk44k'
Begin :ret := subtract_numbers(p1=>:v0, p2=>:v2); End;
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=10601980885
BINDS #2:
Bind#0
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=72 off=0
  kxsbbbfp=1bb2dd60  bln=22  avl=00  flg=05
Bind#1
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=1bb2dd78  bln=22  avl=02  flg=01
  value=48
Bind#2
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=00 csi=00 siz=0 off=48
  kxsbbbfp=1bb2dd90  bln=22  avl=02  flg=01
  value=2

See how ODP.NET generated a little anonymous block and substituted "ret" for the return value parameter? You should see the same thing even if there is no name given to the return parameter. You can also clearly see how the input parameter names are associated with the input values.