How to call a "Stored Procedure" oracle c #

Question

How to call a "Stored Procedure" oracle c #

Navigation

#1 by (1 votes)

1

I have the following procedure in my bank:

I'mtryingtocallmystoreprocedurein.netlikethis:

publicintCountEvent(intresourceId,inteventCounterDefinitionId,DateTimestartDate,DateTimestopDate){intret=0;using(OracleConnectionconn=newOracleConnection(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 I execute my code, it gives the error message:

Incorrect number of argument types in call to 'COUNT_EVENT'

Can anyone help me with why the parameters are wrong?

sql oracle11g .net stored-procedures

asked by anonymous 05.10.2017 / 22:59

1 answer

Pagination in laravel 5.5 Page Bootstrap 4

score 1 · Accepted Answer

Your procedure has an out (out) variable, which should be declared in your call in C # 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 working. The Code to consume it would look something like this:

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

And you'll get it like this:

cmd.Parameters["REC_CUR"].value