Calling Oracle stored procedure from C#?

Question

How does one call a stored procedure in oracle from C#?

Solution 1

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;
    }
}

Solution 2

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

Solution 3

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

• command.CommandText = the name of the stored 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

Solution 4

This Code works well for me calling oracle stored procedure

Add references by right clicking on your project name in solution explorer >Add Reference >.Net then Add namespaces.

using System.Data.OracleClient;
using System.Data;

then paste this code in event Handler

        string str = "User ID=username;Password=password;Data Source=Test";
        OracleConnection conn = new OracleConnection(str);
        OracleCommand cmd = new OracleCommand("stored_procedure_name", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        --Ad parameter list--
        cmd.Parameters.Add("parameter_name", "varchar2").Value = value;
        ....
        conn.Open();
        cmd.ExecuteNonQuery();

And its Done...Happy Coding with C#

Solution 5

Connecting to Oracle is ugly. Here is some cleaner code with a using statement. A lot of the other samples don't call the IDisposable Methods on the objects they create.

using (OracleConnection connection = new OracleConnection("ConnectionString"))
    using (OracleCommand command = new OracleCommand("ProcName", connection))             
    {
          command.CommandType = CommandType.StoredProcedure;
          command.Parameters.Add("ParameterName", OracleDbType.Varchar2).Value = "Your Data Here";
          command.Parameters.Add("SomeOutVar", OracleDbType.Varchar2, 120);
          command.Parameters["return_out"].Direction = ParameterDirection.Output;
          command.Parameters.Add("SomeOutVar1", OracleDbType.Varchar2, 120);
          command.Parameters["return_out2"].Direction = ParameterDirection.Output;
          connection.Open();
          command.ExecuteNonQuery();
          string SomeOutVar = command.Parameters["SomeOutVar"].Value.ToString();
          string SomeOutVar1 = command.Parameters["SomeOutVar1"].Value.ToString();
    }

View more solutions