Niraj Bhatt – Architect's Blog

Ruminations on .NET, Architecture & Design

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
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
Now the C# code to pass parameters to this would be:

SqlConnection conn = new SqlConnection(@”ConnString”);
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;


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
) AS
select count(*) into p_noofrows from dummy d where d.age = p_Age AND = p_name;
–N.B. In above ‘into’ is required, for select returning a recordset you need a RefCursor

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

OracleConnection conn = new OracleConnection(@”ConnString”);
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;

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.

Hope this helps 🙂 .


8 responses to “Passing Optional & Unordered Parameters via ODP.NET – PLS-00306

  1. Selcuk December 7, 2009 at 7:07 pm

    Thank you very much. It was a great help. I thought it would be much easier in my c# application to change the oracle provider but first I had to change the oracletype enums to oracledbtype enums. I had to apply ToString() to procedure output values before casting to expected .net types because they are not of type object anymore. Optional parameter passing became easy with your article.Well, did it worth it ? Yes. Performs better :))

  2. Pic February 9, 2010 at 9:16 pm

    Very helpful!! I was converting a site from using System.Data.OracleClient to OPD.Net and not all the C# methods had the parameters in the correct order. I was getting very frustrated until finding your article. Very nice! Thanks!

  3. Pingback: Migrating SQL Server to Oracle – .NET Perspective « Niraj Bhatt – Architect's Blog

  4. Greg Dillon April 22, 2010 at 2:55 am

    Thank you so much! I could figure out what was going on until I ran across your article. I have to say Oracle approach of changing parameters to be positional (ordered) between by default when switching to Oracle.DataAccess.Client (vs the pre 11g System.Data.OracleClient) seems dangerous and flawed. Is this really the safest approach. Thank goodness my code crashed and I caught this, otherwise I could have had parameters with valid data types that were not ordered and getting set in the wrong fields of my table.

    In addition to this issue (borderline bug in my opinion). The 11.6… version prevents the OracleCommand “Transaction” property from being set (it read-only). This was fixed in 11.7, but both 11.6 and 11.7 have a bug that returns output integer parameters being return by a stored procedure as decimal. This is despite the fact I explicitly state integer parameter an my stored procedure. To get around this I had to the following I had to code the following:

    ImageInfoID = parm.Value.ToInt32

  5. Prashant July 29, 2010 at 12:54 pm

    Thank you very much for this article… I could see that parameters were not passed correctly but was not at all sure….Why .. then saw this article and Problem solved.


  6. Jay January 26, 2011 at 2:10 am

    Thank you very much, this was exactly the problem I was having!

  7. smittrimbakkar November 6, 2012 at 12:10 pm

    Tkanks a lot . You saved my 2-3 working days efforts. Cheers……………..

  8. Pingback: Fix C# Oracle Error Pls-00306 Windows XP, Vista, 7, 8 [Solved]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: