Passing Optional & Unordered Parameters via ODP.NET – PLS-00306

Times change. Recently I was interacting with a team that was using ODP.NET. And they had this stored procedure written in SQL server which they were moving to Oracle DB. They had some challenges in porting their data access code to support Oracle. One of them was – ODP.NET requires all parameters (even if they have default values) to be passed to the Stored Procedure and also expects them in the same order. But with SqlClient the same is not required. Solution to this is use BindByName property of OracleCommand class. Let me elaborate through a small sample code for better understanding:

Consider the following dummy procedure using the above Dummy Table
CREATE PROCEDURE [dbo].[DummyProc]
@Age As Int,
@Name As varchar(50) = ‘AA’ –default value
AS
BEGIN
SET NOCOUNT ON;
DECLARE @noOfRows as INT

SELECT * from Dummy d where d.Age = @Age AND d.Name = @Name

SELECT @noofrows = @@rowcount –tSql is not case sensitive
RETURN @noofrows
END
Now the C# code to pass parameters to this would be:

SqlConnection conn = new SqlConnection(@”ConnString”);
conn.Open();
SqlCommand command = conn.CreateCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = “DummyProc”;
command.Parameters.Add(new SqlParameter(“@Age”, 2)); //Order doesn’t matter
//command.Parameters.Add(new SqlParameter(“@Name”, “AA”)); – This is not required
SqlParameter paramReturnCode = command.CreateParameter();
paramReturnCode.ParameterName = “ReturnCode”;
paramReturnCode.DbType = DbType.Int32;
paramReturnCode.Direction = ParameterDirection.ReturnValue;
command.Parameters.Add(paramReturnCode);
command.ExecuteNonQuery();
Console.WriteLine(command.Parameters[@”ReturnCode”].Value);
conn.Close();

//Output
1

Pretty simple. Let’s move the above to oracle now & access it from ODP.NET. First let’s create a stored procedure in Oracle.

create or replace
PROCEDURE DUMMYPROC
( p_Age IN NUMBER
, p_Name IN VARCHAR2 DEFAULT ‘AA’
, p_NoOfRows OUT NUMBER
) AS
BEGIN
select count(*) into p_noofrows from dummy d where d.age = p_Age AND d.name = p_name;
–N.B. In above ‘into’ is required, for select returning a recordset you need a RefCursor
END DUMMYPROC;

Now let’s write some code using ODP.NET (Oracle.DataAccess.dll)

OracleConnection conn = new OracleConnection(@”ConnString”);
conn.Open();
OracleCommand command = conn.CreateCommand();
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = “DummyProc”;
//command.BindByName = true;
command.Parameters.Add(new OracleParameter(“p_Age”, 2));
//command.Parameters.Add(new OracleParameter(“p_Name”, “AA”)); // This is not required
OracleParameter paramReturnCode = command.CreateParameter();
paramReturnCode.ParameterName = “p_NoOfRows”;
paramReturnCode.OracleDbType = OracleDbType.Int32;
paramReturnCode.Direction = ParameterDirection.Output;
command.Parameters.Add(paramReturnCode);
command.ExecuteNonQuery();
Console.WriteLine(command.Parameters[@”p_NoOfRows”].Value);
conn.Close();

The above code on execution throws an error – PLS-00306: wrong number or types of arguments in call to ‘DUMMYPROC’. The issue here is oracle expects all parameters to be passed in same order for faster execution. But as it turns out that it’s not always the case. Run the same Oracle code above but uncomment line no. 7 – command.BindByName = true

And here you are with the expected output.
//Output
1

Hope this helps 🙂 .