Calling Oracle stored procedure from C#?

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

Instead of

cmd = new OracleCommand("ProcName", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ParName", OracleDbType.Varchar2, ParameterDirection.Input).Value = "foo";

You can also use this syntax:

cmd = new OracleCommand("BEGIN ProcName(:p0); END;", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("ParName", OracleDbType.Varchar2, ParameterDirection.Input).Value = "foo";

Note, if you set cmd.BindByName = False (which is the default) then you have to add the parameters in the same order as they are written in your command string, the actual names are not relevant. For cmd.BindByName = True the parameter names have to match, the order does not matter.

In case of a function call the command string would be like this:

cmd = new OracleCommand("BEGIN :ret := ProcName(:ParName); END;", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("ret", OracleDbType.RefCursor, ParameterDirection.ReturnValue);    
cmd.Parameters.Add("ParName", OracleDbType.Varchar2, ParameterDirection.Input).Value = "foo";
// cmd.ExecuteNonQuery(); is not needed, otherwise the function is executed twice!
var da = new OracleDataAdapter(cmd);
da.Fill(dt);

Below worked for me in .NET Core solution. Note that it uses OracleDataReader and Oracle CommandType is CommandType.Text

using Oracle.ManagedDataAccess.Client;

.......

                string spSql = "BEGIN STORED_PROC_NAME(:IN_PARAM, :OUT_PARAM1, :OUT_PARAM2); END; ";

                using (OracleConnection oraCnn = new OracleConnection(cnnString))
                using (OracleCommand oraCommand = new OracleCommand(spSql, oraCnn))
                {
                    await oraCnn.OpenAsync(cancellationToken);
                    oraCommand.CommandType = CommandType.Text;
                    oraCommand.BindByName = true;

                    oraCommand.Parameters.Add("IN_PARAM", OracleDbType.Long, ParameterDirection.Input).Value = 123;
                    oraCommand.Parameters.Add("OUT_PARAM1", OracleDbType.Int32, null, ParameterDirection.Output);
                    oraCommand.Parameters.Add("OUT_PARAM2", OracleDbType.Varchar2, 4000, null, ParameterDirection.Output);

                    OracleDataReader objReader = oraCommand.ExecuteReader();

                    string outParamValue= oraCommand.Parameters["OUT_PARAM2"].Value.ToString();
                }