Calling Oracle stored procedure from C#?

Please visit this ODP site set up by oracle for Microsoft OracleClient Developers:http://www.oracle.com/technetwork/topics/dotnet/index-085703.html

Also below is a sample code that can get you started to call a stored procedure from C# to Oracle. PKG_COLLECTION.CSP_COLLECTION_HDR_SELECT is the stored procedure built on Oracle accepting parameters PUNIT, POFFICE, PRECEIPT_NBR and returning the result in T_CURSOR.

using Oracle.DataAccess;using Oracle.DataAccess.Client;public DataTable GetHeader_BySproc(string unit, string office, string receiptno){    using (OracleConnection cn = new OracleConnection(DatabaseHelper.GetConnectionString()))    {        OracleDataAdapter da = new OracleDataAdapter();        OracleCommand cmd = new OracleCommand();        cmd.Connection = cn;        cmd.InitialLONGFetchSize = 1000;        cmd.CommandText = DatabaseHelper.GetDBOwner() + "PKG_COLLECTION.CSP_COLLECTION_HDR_SELECT";        cmd.CommandType = CommandType.StoredProcedure;        cmd.Parameters.Add("PUNIT", OracleDbType.Char).Value = unit;        cmd.Parameters.Add("POFFICE", OracleDbType.Char).Value = office;        cmd.Parameters.Add("PRECEIPT_NBR", OracleDbType.Int32).Value = receiptno;        cmd.Parameters.Add("T_CURSOR", OracleDbType.RefCursor).Direction = ParameterDirection.Output;        da.SelectCommand = cmd;        DataTable dt = new DataTable();        da.Fill(dt);        return dt;    }}

I have now got the steps needed to call procedure from C#

   //GIVE PROCEDURE NAME   cmd = new OracleCommand("PROCEDURE_NAME", con);   cmd.CommandType = CommandType.StoredProcedure;   //ASSIGN PARAMETERS TO BE PASSED   cmd.Parameters.Add("PARAM1",OracleDbType.Varchar2).Value = VAL1;   cmd.Parameters.Add("PARAM2",OracleDbType.Varchar2).Value = VAL2;   //THIS PARAMETER MAY BE USED TO RETURN RESULT OF PROCEDURE CALL   cmd.Parameters.Add("vSUCCESS", OracleDbType.Varchar2, 1);   cmd.Parameters["vSUCCESS"].Direction = ParameterDirection.Output;   //USE THIS PARAMETER CASE CURSOR IS RETURNED FROM PROCEDURE   cmd.Parameters.Add("vCHASSIS_RESULT",OracleDbType.RefCursor,ParameterDirection.InputOutput);    //CALL PROCEDURE   con.Open();   OracleDataAdapter da = new OracleDataAdapter(cmd);   cmd.ExecuteNonQuery();   //RETURN VALUE   if (cmd.Parameters["vSUCCESS"].Value.ToString().Equals("T"))   {      //YOUR CODE   }   //OR   //IN CASE CURSOR IS TO BE USED, STORE IT IN DATATABLE   con.Open();   OracleDataAdapter da = new OracleDataAdapter(cmd);   da.Fill(dt);

Hope this helps

It's basically the same mechanism as for a non query command with:

• command.CommandText = the name of thestored procedure
• command.CommandType= CommandType.StoredProcedure
• As many calls to command.Parameters.Add as the number of parameters the sp requires
• command.ExecuteNonQuery

There are plenty of examples out there, the first one returned by Google is this one

There's also a little trap you might fall into, if your SP is a function, your return value parameter must be first in the parameters collection