ORA-06550: row 1, column 7: PLS-00306: invalid number or types of arguments

Question

I have a problem with calling a stored procedure on an 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: row 1, column 7: PLS-00306: invalid number or argument types in the call 'GET_RIT_BY_USER_ID' ORA-06550: row 1, column 7: PL / SQL: the statement is ignored

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

Answer 1

The second parameter is NUMBER , not an integer. Change the second type of parameter to OracleDbType.Decimal

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

Also check the syntax of your Add methods. Now it might be better to specify parameter properties more explicitly, even if it makes the code a bit more verbose:

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

and etc.

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

Answer 2

The most common input parameter problem is null . If kfcKey or user_id is null (either a null reference or Nullable<T> without a value), then for many providers (and therefore I assume that Oracle too) it will not add a parameter . To pass null , you usually need to pass DBNull.Value .

So: check for null s.

 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 the spelling of the parameter, it should match the name of the Store Store variable, especially if you have an output variable. I just spent several hours fixing a similar problem, it turned out that I had the wrong name for my parameter.

Answer 4

You have a specific user type called "type_cursor", but a binding parameter, SYS_REFCURSOR. This is the reason for this error.

Answer 5

I went through a similar problem and found the reason stupid. If your problem is similar, this may help you.

In our case, the exact error message is returned from the package procedure call. After 10 times checking the Java code, its parameters and the back of the Body package and its procedure, we could not understand any differences.

Then we noticed that the package has a similar procedure with a different number of parameters. And the “catch” here is that the package was not compiled using the new method, which is called from the front-end. So, it will be an old procedure.

Please check if this is appropriate for your case.