1. Stored Procedure
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 = "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();
}
}
}
}
2. Oracle Function
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();
}
}
}
}