How to call a "Stored Procedure" oracle c#

Question

I have the following Procedure in my bank:

I’m trying to call my store Procedure in the .net in this way:

        public int CountEvent(int resourceId, int eventCounterDefinitionId, DateTime startDate, DateTime stopDate)
    {         
        int ret = 0;
        using (OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings[MyConnectionStrings.Default].ConnectionString))
        {
            try
            {
                conn.Open();
                Console.WriteLine("ServerVersion: {0} \nDataSource: {1} \nHostName: {2}",
                    conn.ServerVersion, conn.DataSource, conn.HostName);

                OracleCommand com = new OracleCommand("PRODUCTION.COUNT_EVENT", conn);
                OracleParameter paramiter1 = new OracleParameter();
                com.Parameters.Add("@P_RESOURCEID", resourceId);
                com.Parameters.Add("@P_EVENTCOUNTERDEFINITIONID",eventCounterDefinitionId);
                com.Parameters.Add("@P_SHIFT_START", startDate);
                com.Parameters.Add("@P_SHIFT_END", stopDate);
                com.CommandType = System.Data.CommandType.StoredProcedure;
                com.ExecuteReader();

            }
            catch (Exception ex)
            {                    
                Console.WriteLine(ex.Message);
            }
        }            
        return ret;
    }

However, when executing my code, it gives the error message:

Incorrect number of argument types in the call to 'COUNT_EVENT'

Could someone help me in why the parameters are wrong?

Answer 1

Your trial has an output variable (out), which must be declared in your c# call as well.

Consuming an output variable

ora_cmd.Parameters.Add("Lc_Exito", OracleDbType.Int32).Direction = ParameterDirection.Output;

ora_cmd.ExecuteNonQuery();

var returno = ora_cmd.Parameters["Lc_Exito"].value

So your code should work.

Cursor

I also noticed that the out of your query is an oracle cursor, a more complicated type of work. The Code to consume it would be something like this:

cmd.Parameters.Add("REC_CUR",OracleType.Cursor).Direction=ParameterDirection.Output;
con.Open();  
cmd.ExecuteNonQuery();

And you’ll catch him like this:

cmd.Parameters["REC_CUR"].value