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?
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