Oracle c# number function returns 0 always

Question

I am trying to call an Oracle function which has a return type of number. I am calling from C# using OleDB and I understand that the mapping of type number in Oracle is a decimal. Whenever I call this i get a 0 back.

using (OleDbCommand _cmdDueAtDock = new OleDbCommand()) {

   _cmdDueAtDock.Connection = connection;
   _cmdDueAtDock.CommandType = CommandType.StoredProcedure;
   _cmdDueAtDock.CommandText = "IFSAPP.PURCHASE_ORDER_LINE_API.GET_DUE_AT_DOCK";

   _cmdDueAtDock.Parameters.Add(new OleDbParameter() {
       ParameterName = "rv_",
       OleDbType = OleDbType.Decimal,
       Direction = ParameterDirection.ReturnValue
   });
   _cmdDueAtDock.Parameters.Add(new OleDbParameter() {
       ParameterName = "order_no_",
       OleDbType = OleDbType.VarChar,
       Size = 50,
       Direction = ParameterDirection.Input,
       Value = _order_line.ORDER_NO
   });
   _cmdDueAtDock.Parameters.Add(new OleDbParameter() {
       ParameterName = "line_no_",
       OleDbType = OleDbType.VarChar,
       Size = 50,
       Direction = ParameterDirection.Input,
       Value = _order_line.LINE_NO
   });
   _cmdDueAtDock.Parameters.Add(new OleDbParameter() {
       ParameterName = "release_no_",
       OleDbType = OleDbType.VarChar,
       Size = 50,
       Direction = ParameterDirection.Input,
       Value = _order_line.RELEASE_NO
   });

   try {
      _cmdDueAtDock.ExecuteNonQuery();
      dueAtDock = Convert.ToDecimal(_cmdDueAtDock.Parameters["rv_"].Value);
   } catch (Exception ex)  {
      dueAtDock = 0;
   }
}

I am using Oracle client 11.2.0 and the oracle database version is 10.2.0.4.0.

I have read about the 11.1 client having issues, i was actually able to sumulate the behaviour with the 11.1 client by select to_char(function) from dual - I get a correct result without the to_char but a 0 with to_char. Upgrading my client to 11.2 resolve this issue locally.

I am thinking the 11.1 bug made me think it was somebody else's fault - not mine :/ — Dominic Cotton, Dec 01 '15 at 16:53

score 1 · Answer 1 · answered Dec 01 '15 at 16:52

Many thanks for your responses, however it was down to a database transaction, and order of code execution - there was a block of code prior to this one which was executing something that affected the result of the function.

Hope my missing this helps somebody else who is scratching their head!

Dom

score 0 · Answer 2 · answered Dec 01 '15 at 14:22

My guess is the value is always 0 because the catch block is always being tripped. I see one glaring culprit, and another possible. I am presupposing your Stored Procedure looks something like this:

create or replace procedure GET_DUE_AT_DOCK(
  order_no_ in varchar2,
  line_no_ in varchar2,
  release_no_ in varchar2,
  rv_ out number
) as
BEGIN
  rv_ := 999;
END;

I believe your ParameterDirection Property needs to be Output instead of ReturnValue. I could be wrong, but I think ReturnValue is used with a normal SQL command where you have a RETURNING clause, for example:

insert into foo (name, birthday)
values (:NAME, :BIRTHDAY)
returning unique_id into :ID;

I could be wrong, but that's what I think it is.

Secondly, for stored procedures, I think the declaration of the parameters matters and needs to align with the actual parameters in the procedure. If my example above is notionally correct, then you would need to put the parameters in that exact order (order_no, line_no, release_no, rv). Without actually seeing your Stored Procedure, it's hard to say -- you may actually have it right.

So, assuming the SP looked like my example, this should do it:

_cmdDueAtDock.Parameters.Add(new OleDbParameter() {
    ParameterName = "order_no_", OleDbType = OleDbType.VarChar,
    Direction = ParameterDirection.Input, Value = _order_line.ORDER_NO });
_cmdDueAtDock.Parameters.Add(new OleDbParameter() {
    ParameterName = "line_no_", OleDbType = OleDbType.VarChar,
    Direction = ParameterDirection.Input, Value = _order_line.LINE_NO });
_cmdDueAtDock.Parameters.Add(new OleDbParameter() {
    ParameterName = "release_no_", OleDbType = OleDbType.VarChar,
    Direction = ParameterDirection.Input, Value = _order_line.RELEASE_NO });
_cmdDueAtDock.Parameters.Add(new OleDbParameter() {
    ParameterName = "rv_", OleDbType = OleDbType.Decimal,
    Direction = ParameterDirection.Output });   /// <- change is here

try
{
    _cmdDueAtDock.ExecuteNonQuery();
    decimal dueAtDock = Convert.ToDecimal(_cmdDueAtDock.Parameters[3].Value);
}
catch (Exception ex)
{
    decimal dueAtDock = 0;
}

Oracle c# number function returns 0 always

2 Answers2