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