Calling Oracle stored procedure from C#?

by

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

More Related Contents:

  1. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  2. Call a stored procedure with parameter in c#
  3. Calling stored procedure with return value
  4. Get output parameter value in ADO.NET
  5. When should “SqlDbType” and “size” be used when adding SqlCommand Parameters?
  6. SQLite Database Locked exception
  7. Populate data table from data reader
  8. Difference with Parameters.Add and Parameters.AddWithValue
  9. SqlConnection SqlCommand SqlDataReader IDisposable
  10. Exception when AddWithValue parameter is NULL
  11. Handling ExecuteScalar() when no results are returned
  12. Insert into C# with SQLCommand
  13. Can the Oracle managed driver use async/await properly?
  14. MultipleActiveResultSets=True or multiple connections?
  15. How DataReader works?
  16. How to keep single SQL Server connection instance open for multiple request in C#?
  17. How can I deploy a .NET application that uses ODAC without installing the whole component to the user?
  18. OracleParameter and IN Clause
  19. DataTable equivalent in Java [duplicate]
  20. How do I re-write a SQL query as a parameterized query?
  21. OraOLEDB.Oracle provider is not registered on the local machine
  22. Add WHERE clauses to SQL dynamically / programmatically
  23. Check if a SQL table exists
  24. How to return oracle output parameters from a stored procedure in .NET
  25. What is the difference between dataview and datatable?
  26. Reading a date using DataReader
  27. What ‘length’ parameter should I pass to SqlDataReader.GetBytes()
  28. How do I connect to a SQL database from C#?
  29. .net connection pooling
  30. How do I return XML from a Stored Procedure?

Leave a Comment