Using parameters is not clear

[torely]

Using parameters is not clear
I ‘m using parameters with stored procedures and components behavior is not so that I expect.
Here is the sample that should help you understand what exactly I mean.

1st scenario (using OracleCommand with ParameterCheck = false)
Package:

Code: Select all

create or replace package TEST is
  type REF_CURSOR is ref cursor;
end TEST;

create or replace package body TEST is
  Function Sample1(
    P1 in number,
    P2 in varchar := ‘test’,
    P3 in varchar := ‘test2’) return REF_CURSOR;
Begin
 …
End;

end TEST;

C# code:

Code: Select all

pRes = new OracleParameter("RETURN_VALUE", OracleDbType.Cursor);
pRes.Direction = ParameterDirection.ReturnValue;

oracleCommand1.CommandText = " TEST. Sample1";

oracleCommand1.Parameters.Clear();
oracleCommand1.Parameters.Add("P1 ", 1);
oracleCommand1.Parameters.Add("P3 ", "not test");
oracleCommand1.Parameters.Add(pRes);
oracleCommand1.ExecuteNonQuery();

It works, but value of P3 parameter is assigned to P2 parameter and it doesn’t depend of the parameter name (i.e. I can write oracleCommand1.Parameters.Add("aaa ", "not test") and it still aasignes value “not test” to P2 parameter) it depends only of the order of parameter.
Here is serious problem. I should always check parameters order because if write

Code: Select all

oracleCommand1.Parameters.Add("P1 ", 1);
oracleCommand1.Parameters.Add("P3 ", "param3");
oracleCommand1.Parameters.Add("P2 ", "param2");

then indeed P2 value will be “param3” and P3 value “param2”.

2nd scenario (using OracleCommand with ParameterCheck = true)
Package:

Code: Select all

create or replace package TEST is
  type REF_CURSOR is ref cursor;
end TEST;

create or replace package body TEST is
  Function Sample1(
    P1 in number,
    P2 in varchar := ‘test’,
    P3 in varchar := ‘test2’) return REF_CURSOR;
Begin
 …
End;

  Function Sample1(
    P1 in number,
    P2 in varchar := ‘test’) return REF_CURSOR;
Begin
 …
End;

end TEST;

C# code:

Code: Select all

pRes = new OracleParameter("RETURN_VALUE", OracleDbType.Cursor);
pRes.Direction = ParameterDirection.ReturnValue;

oracleCommand1.CommandText = " TEST. Sample1";

oracleCommand1.Parameters.Clear();
oracleCommand1.Parameters.Add("P1 ", 1);
oracleCommand1.Parameters.Add("P3 ", "not test");
oracleCommand1.Parameters.Add(pRes);
oracleCommand1.ExecuteNonQuery();

In this case it will not work because return parameter name is unknown (even if there no return parameter (for procedure) I loose my defaul values as it described in help). And another problem: How components will define what function to use with 3 or 2 parameters (as it described in help, all unassigned parameters values will set to NULL).

Exactly what I need is to use OracleCommand with ParameterCheck = false, and not to check parameters order.

[AndreyR]

The OracleCommand class will contain the new PassParametersByName property.
Setting it to true will fix the situation.
These improvements are available in the new build of dotConnect for Oracle.

[torely]

The version of my dotConnect for Oracle is 5.00.22. It is the latest release and there no such a property.
Is this functionality realized in 5.20 Beta?
Can you inform me when this functionality will be realized in the release?

[AndreyR]

We plan to release the new 5.0.25 Release build of dotConnects this week. It will contain the requested fix.