Call Oracle from ADO.NET: C#, OCI: Process multiple Cursors: Listing 4

Invoke the NextResult method in the instance of OracleDataReader (objReader) in order to advance to the next cursor.

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_employee_department";
            objCmd.CommandType = 
               CommandType.StoredProcedure;
            objCmd.Parameters.Add("cur_employee", 
               OracleType.Cursor).Direction = 
               ParameterDirection.Output;
            objCmd.Parameters.Add("cur_department", 
               OracleType.Cursor).Direction = 
               ParameterDirection.Output;

            try
            {
               objConn.Open();
               OracleDataReader objReader = 
                  objCmd.ExecuteReader();
               PrintReader(objReader);
               objReader.NextResult();
               PrintReader(objReader);
            }
            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.