Using parameters is not clear
Posted: Thu 26 Mar 2009 02:41
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:
C# code:
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
then indeed P2 value will be “param3” and P3 value “param2”.
2nd scenario (using OracleCommand with ParameterCheck = true)
Package:
C# code:
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.
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;
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();
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");
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;
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();
Exactly what I need is to use OracleCommand with ParameterCheck = false, and not to check parameters order.