PLS-00306: wrong number or types of arguments error in C#

Question

We are using an Oracle stored procedure in a C# program, which was working fine until we added the 'v_nameurn' variable.

Here's the Oracle procedure:

create or replace procedure stats_By_Date (
   v_SDate   IN varchar2, 
   v_EDate   IN varchar2, 
   v_user    IN number, 
   v_nameurn IN number,
   p_cursor  OUT SYS_REFCURSOR
) as 
BEGIN
   OPEN p_cursor FOR
     select T1.Staff_No, 
            T3.Title, 
            T2.Verno, 
            To_char(T1.Response_dt, 'DD/MM/YYYY'), 
            T5.Response, 
            T6.Forenames||' '||T6.Surname
       from TOU.Users_version_link T1, 
            TOU.Version T2, 
            TOU.name T3, 
            TOU.Admin_Access T4, 
            Tou.CV_Response T5, 
            corp.wforce_employee@remote_DWLive T6
      where T1.Version_urn=T2.Urn
        and T1.Name_urn= T3.urn
        and T3.urn = T4.name_urn
        and T1.Response_urn=T5.urn
        and T1.staff_no=T6.Staff_Number
        and Trunc(T1.Response_dt) >= To_date(v_Sdate, 'DD/MM/YYYY')
        and Trunc(T1.Response_dt) <= To_date(v_EDate, 'DD/MM/YYYY')
        and T4.Staff_No = v_user
        and T3.urn = v_nameurn;
End;

And here is the C# code:

this.ConnectToDb();
var cmd = new OracleCommand
{
   Connection = cn,
   CommandText = "TOU.Stats_By_Date",
   CommandType = CommandType.StoredProcedure
 };
 var id = HttpContext.Current.User.Identity.Name.Split('\\')[1].ToString();
 cmd.Parameters.Add("v_SDate", OracleDbType.Varchar2).Value = 
          startdate.ToString("dd-MM-yyyy");
 cmd.Parameters.Add("v_EDate", OracleDbType.Varchar2).Value = 
          enddate.ToString("dd-MM-yyyy");
 cmd.Parameters.Add("v_user", OracleDbType.Decimal).Value = int.Parse(id);
 cmd.Parameters.Add("v_nameurn", OracleDbType.Decimal).Value = 3;
 var rc = cmd.Parameters.Add("p_cursor", OracleDbType.RefCursor);
 rc.Direction = ParameterDirection.Output;
 cmd.ExecuteNonQuery();
 this.DisconnectFromDb(); 

Any suggestions as to what could be wrong please? There are 5 total variables in both pieces of code (4 in, 1 out), which is fine and all of the data types seem to match. I've also tried different variations on the actual v_nameurn variable, so I've tried 3, "3", and defining it as an Int variable beforehand.

Answer 1

Decimal is Oracle NUMBER type.. So Change your datatype for v_user and 'v_nameurn'

cmd.Parameters.Add("v_user", OracleDbType.Decimal).Value = int.Parse(id);
cmd.Parameters.Add("v_nameurn", OracleDbType.Decimal).Value = 3;

OracleDbType Enumeration

Answer 2

cmd.Parameters.Add("v_user", OracleDbType.Decimal).Value = decimal.Parse(id);
cmd.Parameters.Add("v_nameurn", OracleDbType.Decimal).Value = 3m;

You must use a decimal when you are dealing with decimals. For Oracle, decimals don't translate well to ints and the other way round.