CODEWITHSUNDEEP
c#oracle-databaseodp.net
Rocco Jr.
Rocco Jr.

Reputation: 77

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

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?

Upvotes: 6

Views: 94048

Answers (5)

sbm
sbm

Reputation: 81

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.

Upvotes: 0

Christian Shay
Christian Shay

Reputation: 2635

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.

Upvotes: 0

Manny
Manny

Reputation: 1034

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.

Upvotes: 3

D Stanley
D Stanley

Reputation: 152566

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

Upvotes: 7

Marc Gravell
Marc Gravell

Reputation: 1062955

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

Upvotes: 6

Related Questions