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

6
votes

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?

5
c#oracleodp.net
I miss the variable p_id_utente in the list of parameters (you have a P_USER though). The other two are there. – Independent
this is not the problem, I've checked all the names of the parameters – Rocco Jr.
Have you looked at this SO question: stackoverflow.com/questions/6360244/… – DCookie

5 Answers

7
votes

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

6
votes

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);
3
votes

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.

0
votes

You have some kind of user defined type called "type_cursor" but are binding a SYS_REFCURSOR parameter. That is the cause of this error.

0
votes

I had gone through similar issue and found the root cause silly. If your problem is similar, this may help you.

In our case, the exact error message is being returned from a package's procedure call. After 10's of times validating the Java code, its parameters and the back end Package "Body" and its procedure we couldn't figure out any differences.

Then, we noticed that that package has similar procedure with different number of parameters. And the "catch" here is that the package wasn't compiled with the new method that is being called from front end. So, it is going to the old procedure.

Please check if this is same with your case.