Call Oracle from ADO.NET: C#, OCI: Call the get_count_emp_by_dept Function: Listing 2

You need to define a return parameter (return_value) to get the result.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace CallingOracleStoredProc
{
   class Program
   {
      static void Main(string[] args)
      {
         using (OracleConnection objConn = new 
            OracleConnection(
               "Data Source=orcl; User ID=scott; 
               Password=tiger")) 
         {
            OracleCommand objCmd = new 
               OracleCommand();
            objCmd.Connection = objConn;
            objCmd.CommandText = 
               "get_count_emp_by_dept";
            objCmd.CommandType = 
               CommandType.StoredProcedure;
            objCmd.Parameters.Add("pin_deptno", 
               OracleType.Number).Value = 20;
            objCmd.Parameters.Add("return_value", 
               OracleType.Number).Direction = 
               ParameterDirection.ReturnValue;

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

            objConn.Close();
         }
      }
   }
}
comments powered by Disqus
Upcoming Events

.NET Insight

Sign up for our newsletter.

I agree to this site's Privacy Policy.