Call Oracle from ADO.NET: C#, OCI: Invoke the get_departments Procedure: Listing 3
Get a cursor by defining a cursor parameter with ParameterDirection set to Output, and call the ExecuteReader method in the OracleCommand instance (objCmd) to receive the cursor and print the result set referenced by the OracleDataReader instance (objReader).
using System;
using System.Text;
using System.Data.OracleClient;
using System.Data;
namespace Invoke_PLSQL_Code
{
class Program
{
internal static void PrintReader(OracleDataReader
objReader)
{
for (int i = 0; i < objReader.FieldCount; i++)
{
System.Console.Write("{0}\t",
objReader.GetName(i));
}
System.Console.Write("\n");
while (objReader.Read())
{
for (int i = 0; i < objReader.FieldCount; i++)
{
System.Console.Write("{0}\t",
objReader[i].ToString());
}
System.Console.Write("\n");
}
}
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 =
"human_resources.get_departments";
objCmd.CommandType =
CommandType.StoredProcedure;
objCmd.Parameters.Add("cur_department",
OracleType.Cursor).Direction =
ParameterDirection.Output;
try
{
objConn.Open();
OracleDataReader objReader =
objCmd.ExecuteReader();
PrintReader(objReader);
}
catch (Exception ex)
{
System.Console.WriteLine("Exception: {0}",
ex.ToString());
}
objConn.Close();
}
}
}
}