ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments

Question

I have problem with calling store procedure on Oracle 11g server.

stored procedure

PROCEDURE get_rit_by_user_id(KDC_KEY      IN VARCHAR2,
                             p_id_utente IN NUMBER,
                             p_cur_out   OUT type_cursor) IS
  BEGIN
    ...
    ...
    ...
  END

c# code

OracleCommand cmd = new OracleCommand();
cmd.Connection = oracleConnection;
cmd.CommandText = userIdEsercizio + packageName + "GET_RIT_BY_USER_ID";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("KDC_KEY", OracleDbType.Varchar2, kdcKey, ParameterDirection.Input);
cmd.Parameters.Add("P_ID_UTENTE", OracleDbType.Int32, user_id, ParameterDirection.Input);
cmd.Parameters.Add("P_CUR_OUT", OracleDbType.RefCursor, ParameterDirection.Output);

OracleDataReader reader = cmd.ExecuteReader();

cmd.ExecuteReader() throws this exception:

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'GET_RIT_BY_USER_ID' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

What is wrong with the above code that it gets a wrong number of types of arguments error?

Answer 1

Your second parameter is a NUMBER, not an integer. Change the second parameter type to OracleDbType.Decimal

http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleDbTypeEnumerationType.htm

Also check the syntax of your Add methods. It may be better for now to specify the parameter properties more explicitly, even if it makes the code a little more verbose:

cmd.Parameters.Add(
    new OracleParameter() 
        {
            ParameterName="KDC_KEY", 
            DbType=OracleDbType.Varchar2, 
            Value=kdcKey, 
            Direction=ParameterDirection.Input
        } 
    );

etc.

Is the proc returning a result set in addition to the cursor? If not use ExecuteNonQuery instead of Execute

Answer 2

The most common issue with input parameters is null. If kfcKey or user_id is null (either a null-reference, or a Nullable<T> without a value), then for many providers (and I therefore assume Oracle too) it won't add the parameter. To pass a null, you usually need to pass DBNull.Value instead.

So: check for nulls.

cmd.Parameters.Add("KDC_KEY", OracleDbType.Varchar2,
    (object)kdcKey ?? DBNull.Value, ParameterDirection.Input);
cmd.Parameters.Add("P_ID_UTENTE", OracleDbType.Int32,
    (object)user_id ?? DBNull.Value, ParameterDirection.Input);

Answer 3

Check your parameter spelling, it has to match the Store Procedure variable name, specially, if you have an output variable. I just spent a few hours troubleshooting a similar issue, it turned out I had misspelled my output parameter name.