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#.
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..
This is how I addressed my requirement
//sample result entity
public class SearchResult
{
public int NumberOfPagesAvailable { get; set; }
public int CurrentPageNumber { get; set; }
public IEnumerable<ResultItem> ResultItems { get; set; }
}
/// <summary>
/// Method to Create an output parameter
/// </summary>
/// <param name="paramName">The Parameter name</param>
/// <param name="paramType">Type of the parameter</param>
/// <param name="value">The value to set</param>
/// <returns></returns>
public static OracleParameter CreateOutputParameter(string paramName, OracleDbType paramType, object value)
{
var outParam = new OracleParameter(paramName, paramType, ParameterDirection.Output)
{
Value = value ?? DBNull.Value
};
return outParam;
}
//1 create command
//2 created the params as below
OracleParameter pages = OracleHelper.CreateOutputParameter("p_pages", OracleDbType.Int64, null);
OracleParameter pageNumber = OracleHelper.CreateOutputParameter("p_pageNumber", OracleDbType.Int64, null);
OracleParameter resultSet = OracleHelper.CreateOutputParameter("p_resultSet");
//3 execute using oralce command
//4 read the param values as below after executing the command
if (pages.Value != null)
{
result.NumberOfPagesAvailable = Convert.ToInt32(pages.Value.ToString());
}
if (pageNumber.Value != null)
{
result.CurrentPageNumber = Convert.ToInt32(pageNumber.Value.ToString());
}
if (resultSet.Value != null && result.NumberOfPagesAvailable>0 && result.CurrentPageNumber>0)
{
OracleRefCursor refCursor;
using (refCursor = (OracleRefCursor)resultSet.Value)
{
using (OracleDataReader rdr = refCursor.GetDataReader())
{
//iterate through the loop and read the values from the reader
//set ResultItems
}
}
}