Call Oracle from ADO.NET: C#, OCI: Call the Procedure count_emp_by_dept from .NET: Listing 1

First, establish a connection to the server by creating an instance of OracleConnection (objConn), set up the OracleCommand instance (objCmd), and then invoke its ExecuteNonQuery method in order to call the stored procedure.

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

namespace CallingOracleStoredProc
{
   class Program
   {

      static void CallProcedure()
      {

         using (OracleConnection objConn = new 
            OracleConnection("Data Source=ORCL; User 
            ID=scott; Password=tiger"))
         {
            OracleCommand objCmd = new 
               OracleCommand();
            objCmd.Connection = objConn;
            objCmd.CommandText = "count_emp_by_dept";
            objCmd.CommandType = 
               CommandType.StoredProcedure;
            objCmd.Parameters.Add("pin_deptno", 
               OracleType.Number).Value = 20;
            objCmd.Parameters.Add("pout_count", 
               OracleType.Number).Direction = 
               ParameterDirection.Output;

            try
            {
               objConn.Open();
               objCmd.ExecuteNonQuery();
               System.Console.WriteLine( 
                  "Number of employees in department 20 is {0}", 
                  objCmd.Parameters["pout_count"].Value);
            }
            catch (Exception ex)
            {
               System.Console.WriteLine(
                  "Exception: {0}", ex.ToString());
            }

            objConn.Close();
         }
      }

      static void Main(string[] args)
      {
         CallProcedure();
      }
   }
}
comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.