Calling Oracle Stored Procedure from C#

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

How to call an Oracle Procedure from C#

I don't think you're that far off... try this:

OracleCommand cmd = new OracleCommand("return_num", Oraclecon);
cmd.Parameters.Add(new OracleParameter("xNum", OracleDbType.Decimal,
    ParameterDirection.Output));
cmd.CommandType = CommandType.StoredProcedure;

cmd.ExecuteNonQuery();
OracleDecimal d = (OracleDecimal)cmd.Parameters[0].Value;
double result = d.ToDouble();

result now contains the out parameter from the procedure.

I think your problem is you were attempting to use a DbDataReader on a stored procedure. DbDataReader is for queries.

Also, I used ODP.net -- that may or may not have contributed to your issue, that you were using Ole.

Calling a stored Oracle procedure in C#

Did you check any documentation, this is pretty basic:

string constr = "User Id=scott;Password=tiger;Data Source=oracle";
OracleConnection con = new OracleConnection(constr);
con.Open();

OracleCommand cmd = new OracleCommand("BEGIN p_delete_tt_duration_10min; END;", con);
// Auto-commit changes
cmd.ExecuteNonQuery();

// Clean up
cmd.Dispose();
con.Dispose();

You can also write

OracleCommand cmd = new OracleCommand("p_delete_tt_duration_10min", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();

Call Oracle procedure from C# when procedure is in different schema

When you specify CommandType.StoredProcedure, the CommandText should be the name of the procedure, not a PL/SQL script. The parameter is passed in the Parameters collection.

using (OracleConnection connection = new OracleConnection(oracleStgConnectionString))
using (OracleCommand command = connection.CreateCommand())
{
    connection.Open();

    command.BindByName = true;
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "OtherSchema.P_CloseBatch";

    command.Parameters.Add(new OracleParameter()
    {
        ParameterName = "batchname",
        OracleDbType = OracleDbType.NVarchar2
        Value = parameterBatchName
    });

    command.ExecuteNonQuery();

    // Call to connection.Close removed as the "using" block already does that.
}

I've thrown in a few other changes:

• Created command using connection.CreateCommand because it assigns the connection before returning the object.

• A using block on command because it's also disposable.

• Setting BindByName so the parameter name I specify actually means something.

Call Oracle Stored Procedure and Package with Enterprise Library

Change your stored procedure name to:

SP_NAME = "package_name.Get_Data";

To add a SYS_REFCURSOR out parameter use this:

OracleParameter oraP = new OracleParameter();
oraP.OracleDbType = OracleDbType.RefCursor;
oraP.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(oraP);

How to execute Oracle stored procedure with User Defined DataTypes Parameters in Oracle from .NET Front-End

I don't know anything about tools you're using, but - as type is declared within the package, I'd say that this:

ora_Id.UdtTypeName = "t_number";

should've been

ora_Id.UdtTypeName = "SampleSchema.SamplePackage.t_number";

just like you used for the procedure itself.

---

Other than that, as type you use is a table of numbers, you could try to use Oracle's built-in type: sys.odcinumberlist instead.

---

Related Topics