How to read different type of out parameters from oracle stored procedure?

Question

I have an oracle stored procedure with two numeric output parameters and one ref cursor. below is the structure of the procedure

procedure SearchData
(
    p_search in number
    ,p_pages out number
    ,p_pageNumber out number
    ,p_resultSet out sys_refcursor
)

I need to read them using c#.

Answer 1

First you should install oracle client for c# eg: I always use

using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

You need to download it from oracle website to use it in c#

then you can call procedure like below

   OracleCommand cmd = con.CreateCommand(); //con is the oracle connection
     cmd.CommandType = CommandType.StoredProcedure;
     cmd.Connection = con;
    cmd.CommandText = "SearchData";

  OracleParameter p_search = new OracleParameter();
  p_search.OracleDbType = OracleDbType.Int64;
  p_search.Direction = ParameterDirection.Input;
  p_search.Value = .....;

  OracleParameter p_pages = new OracleParameter();
  p_pages.OracleDbType = OracleDbType.Int64;
  p_pages.Direction = ParameterDirection.Output;
  p_pages.Size = 1000;

 OracleParameter p_pageNumber = new OracleParameter();
 p_pageNumber.OracleDbType = OracleDbType.Int64;
 p_pageNumber.Direction = ParameterDirection.Output;
 p_pageNumber.Size = 1000;

 OracleParameter p_resultSet = new OracleParameter();
 p_resultSet.OracleDbType = OracleDbType.RefCursor;
 p_resultSet.Direction = ParameterDirection.Output;

cmd.Parameters.Add(p_search);
cmd.Parameters.Add(p_pages);
cmd.Parameters.Add(p_page_number);
cmd.Parameters.Add(p_resultSet);

con.Open();
cmd.ExecuteNonQuery();

After execution u can call the out parameter to get the value..like

string pages = p_pages.Value.ToString()

In case of reference cursor we need to use oracle reader

OracleDataReader rd = ((OracleRefCursor)cmd.Parameters[3].Value).GetDataReader(); 
//3 is for 4rth parameter bcos parameter index start from 0

then read rd to get the values..