Oracle

  function get_emp_info return sys_refcursor;
  procedure get_emp_info(p_rc out sys_refcursor);

  procedure get_multiple_cursors(p_rc1 out sys_refcursor, p_rc2 out sys_refcursor, p_rc3 out sys_refcursor);
end;
/

create or replace package body otn_ref_cursor as
  function get_emp_info return sys_refcursor is
    -- declare the cursor variable
    -- sys_refcursor is a built in type
    l_cursor sys_refcursor;
  begin
    open l_cursor for
    select   employee_id,
             last_name,
             first_name,
             to_char(hire_date, 'DD-MON-YYYY') hire_date
    from     employees
    where    last_name like 'A%'
    order by last_name,
             first_name;

    return l_cursor;
  end;

  procedure get_emp_info(p_rc out sys_refcursor) is
  begin
    -- open the cursor using the passed in ref cursor
    -- sys_refcursor is a built in type
    open p_rc for
    select   employee_id,
             last_name,
             first_name,
             to_char(hire_date, 'DD-MON-YYYY') hire_date
    from     employees
    where    last_name like 'A%'
    order by last_name,
             first_name;
  end;

  procedure get_multiple_cursors(p_rc1 out sys_refcursor, p_rc2 out sys_refcursor, p_rc3 out sys_refcursor) is
  begin
    -- open the cursors using the passed in ref cursor parameters
    -- sys_refcursor is a built in type
    open p_rc1 for
    select   employee_id,
             last_name,
             first_name,
             to_char(hire_date, 'DD-MON-YYYY') hire_date
    from     employees
    where    last_name like 'A%'
    order by last_name,
             first_name;

    open p_rc2 for
    select   employee_id,
             last_name,
             first_name,
             to_char(hire_date, 'DD-MON-YYYY') hire_date
    from     employees
    where    last_name like 'B%'
    order by last_name,
             first_name;

    open p_rc3 for
    select   employee_id,
             last_name,
             first_name,
             to_char(hire_date, 'DD-MON-YYYY') hire_date
    from     employees
    where    last_name like 'C%'
    order by last_name,
             first_name;
  end;
end;






--------------------
using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;


static void GetCursorFunction(OracleConnection con)
    {
      // display a simple marker line to the console
      // to indicate where we are
      Console.WriteLine("In GetCursorFunction...");
      Console.WriteLine();

      // create the command object and set attributes
      OracleCommand cmd = new OracleCommand("otn_ref_cursor.get_emp_info", con);
      cmd.CommandType = CommandType.StoredProcedure;

      // create parameter object for the cursor
      OracleParameter p_refcursor = new OracleParameter();

      // this is vital to set when using ref cursors
      p_refcursor.OracleDbType = OracleDbType.RefCursor;

      // this is a function return value so we must indicate that fact
      p_refcursor.Direction = ParameterDirection.ReturnValue;

      // add the parameter to the collection
      cmd.Parameters.Add(p_refcursor);

      // create a data adapter to use with the data set
      OracleDataAdapter da = new OracleDataAdapter(cmd);

      // create the data set
      DataSet ds = new DataSet();

      // fill the data set
      da.Fill(ds);

      // display the data to the console window
      DisplayRefCursorData(ds);

      // clean up our objects release resources
      ds.Dispose();
      da.Dispose();
      p_refcursor.Dispose();
      cmd.Dispose();

      Console.WriteLine();
    }

    static void GetCursorParameter(OracleConnection con)
    {
      // display a simple marker line to the console
      // to indicate where we are
      Console.WriteLine("In GetCursorParameter...");
      Console.WriteLine();

      // create the command object and set attributes
      OracleCommand cmd = new OracleCommand("otn_ref_cursor.get_emp_info", con);
      cmd.CommandType = CommandType.StoredProcedure;

      // create parameter object for the cursor
      OracleParameter p_refcursor = new OracleParameter();

      // this is vital to set when using ref cursors
      p_refcursor.OracleDbType = OracleDbType.RefCursor;

      // this is an output parameter so we must indicate that fact
      p_refcursor.Direction = ParameterDirection.Output;

      // add the parameter to the collection
      cmd.Parameters.Add(p_refcursor);

      // create a data adapter to use with the data set
      OracleDataAdapter da = new OracleDataAdapter(cmd);

      // create the data set
      DataSet ds = new DataSet();

      // fill the data set
      da.Fill(ds);

      // display the data to the console window
      DisplayRefCursorData(ds);

      // clean up our objects release resources
      ds.Dispose();
      da.Dispose();
      p_refcursor.Dispose();
      cmd.Dispose();

      Console.WriteLine();
    }

    static void TraverseResultSets(OracleConnection con)
    {
      // display a simple marker line to the console
      // to indicate where we are
      Console.WriteLine("In TraverseResultSets...");
      Console.WriteLine();

      // create the command object and set attributes
      OracleCommand cmd = new OracleCommand("otn_ref_cursor.get_multiple_cursors", con);
      cmd.CommandType = CommandType.StoredProcedure;

      // create parameter objects for the cursors
      OracleParameter p_rc1 = new OracleParameter();
      OracleParameter p_rc2 = new OracleParameter();
      OracleParameter p_rc3 = new OracleParameter();

      // this is vital to set when using ref cursors
      p_rc1.OracleDbType = OracleDbType.RefCursor;
      p_rc2.OracleDbType = OracleDbType.RefCursor;
      p_rc3.OracleDbType = OracleDbType.RefCursor;

      // these are output parameters so we must indicate that fact
      p_rc1.Direction = ParameterDirection.Output;
      p_rc2.Direction = ParameterDirection.Output;
      p_rc3.Direction = ParameterDirection.Output;

      // add the parameters to the collection
      cmd.Parameters.Add(p_rc1);
      cmd.Parameters.Add(p_rc2);
      cmd.Parameters.Add(p_rc3);

      // work with an OracleDataReader rather
      // than a DataSet to illustrate ODP.NET features
      OracleDataReader dr = cmd.ExecuteReader();

      // display the data in the first ref cursor
      Console.WriteLine("Displaying ref cursor #1:");
      DisplayRefCursorData(dr);
      Console.WriteLine();

      // the Oracle Data Provider follows the standard
      // by exposing the NextResult method to traverse
      // multiple result sets
     
      // display the data in the second ref cursor
      if (dr.NextResult())
      {
        Console.WriteLine("Displaying ref cursor #2:");
        DisplayRefCursorData(dr);
        Console.WriteLine();
      }

      // display the data in the third ref cursor
      if (dr.NextResult())
      {
        Console.WriteLine("Displaying ref cursor #3:");
        DisplayRefCursorData(dr);
        Console.WriteLine();
      }

      // clean up our objects and release resources
      dr.Dispose();
      p_rc1.Dispose();
      p_rc2.Dispose();
      p_rc3.Dispose();
      cmd.Dispose();
    }

    static void MultipleActiveResultSets(OracleConnection con)
    {
      // display a simple marker line to the console
      // to indicate where we are
      Console.WriteLine("In MultipleActiveResultSets...");
      Console.WriteLine();

      // create the command object and set attributes
      OracleCommand cmd = new OracleCommand("otn_ref_cursor.get_multiple_cursors", con);
      cmd.CommandType = CommandType.StoredProcedure;

      // create parameter objects for the cursors
      OracleParameter p_rc1 = new OracleParameter();
      OracleParameter p_rc2 = new OracleParameter();
      OracleParameter p_rc3 = new OracleParameter();

      // this is vital to set when using ref cursors
      p_rc1.OracleDbType = OracleDbType.RefCursor;
      p_rc2.OracleDbType = OracleDbType.RefCursor;
      p_rc3.OracleDbType = OracleDbType.RefCursor;

      // these are output parameters so we must indicate that fact
      p_rc1.Direction = ParameterDirection.Output;
      p_rc2.Direction = ParameterDirection.Output;
      p_rc3.Direction = ParameterDirection.Output;

      // add the parameters to the collection
      cmd.Parameters.Add(p_rc1);
      cmd.Parameters.Add(p_rc2);
      cmd.Parameters.Add(p_rc3);

      // execute the command to open the ref cursors
      cmd.ExecuteNonQuery();

      // work with an OracleDataReader rather
      // than a DataSet to illustrate ODP.NET features
      OracleDataReader dr1 = ((OracleRefCursor) p_rc1.Value).GetDataReader();

      // notice we are skipping the second (or "middle") ref cursor
      OracleDataReader dr3 = ((OracleRefCursor) p_rc3.Value).GetDataReader();

      // illustrate the multiple result sets are active
      // by "randomly" dislaying data from each one
      if (dr1.Read())
      {
        Console.WriteLine("Displaying data from ref cursor #1:");
        DisplayDataReaderRow(dr1);
        Console.WriteLine();
      }

      if (dr3.Read())
      {
        Console.WriteLine("Displaying data from ref cursor #3:");
        DisplayDataReaderRow(dr3);
        Console.WriteLine();
      }

      if (dr1.Read())
      {
        Console.WriteLine("Displaying data from ref cursor #1:");
        DisplayDataReaderRow(dr1);
        Console.WriteLine();
      }
     
      if (dr3.Read())
      {
        Console.WriteLine("Displaying data from ref cursor #3:");
        DisplayDataReaderRow(dr3);
        Console.WriteLine();
      }

      // clean up our objects and release resources
      dr1.Dispose();
      dr3.Dispose();
      p_rc1.Dispose();
      p_rc2.Dispose();
      p_rc3.Dispose();
      cmd.Dispose();
    }