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.