Oracle error: PLS-00306: wrong number or types of arguments

Question

I'm using Dapper and ODP.Net to call a stored procedure. I feel dumb not to find what's wrong with my parameters but here it goes:

Here is the stored procedure signature:

PROCEDURE SP_NETWORK_GETALL(UserLogon IN VARCHAR2, NetworkVersionList OUT refCursor)

And here is my .Net call:

using (var conn = new OracleConnection(connString))
{
    var parameters = new List<OracleParameter>
    {
        new OracleParameter()
        {
            Direction = ParameterDirection.Input,
            ParameterName = "UserLogon",
            OracleDbType = OracleDbType.Varchar2,
            Size = 4000,
            Value = "TEST"
        },
        new OracleParameter()
        {
            Direction = ParameterDirection.Output,
            OracleDbType = OracleDbType.RefCursor,
            ParameterName = "NetworkVersionList",
            Value = DBNull.Value
        },
    };

    var results = conn.Query("SDTM.PKG_SP_GET.SP_NETWORK_GETALL", parameters, commandType: CommandType.StoredProcedure);
}

I've tried this stored procedure, it works. I have used Dapper for other stored procedures, it works as well. I have tried changing the order of the parameters (the ref cursor first), setting the size of the VarChar2 parameter or not, DBNull.Value for the ref cursor or not.

I have seen a million questions like this one on stackoverflow or on internet, but I can't see the parameter mismatch here...

Answer 1

The problem is that you're sending a list of OracleParameter objects to dapper's "parameters" variable. Dapper is meant to be provider agnostic and you're sending provider specific types.

Take a look at the GetCacheInfo method in the dapper code and you'll see three cases in the way that dapper attempts to handle parameters:

1. An object of IDynamicParameters
2. An object of IEnumerable< KeyValuePair< string, object>>
3. Default/Else - Attempt to find properties in your object that match the tokens in the command.

You're probably falling into the default case, yet because you have a proc, there are no tokens in the command and no parameters are added. Even if they were, it would probably be searching thru the properties of type List<> and it's not going to find a match.

Adding IDynamicParameters puts you in control of the parameters and that's why it works. This has nothing to do with odp.net.

I suppose this is one of my irks with dapper as with all ORMs - as hard as they will try, there's always going going to be provider specific stuff that can't be abstracted away. That's why I prefer to simply setup the provider specific command and then use a utility class that ONLY does the mapping (without concerning itself with the connection/setup/execution).