oracle存储过程out类型,如何从oracle存储过程中读取不同类型的out参数?

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#.

Talk1:

is it working?

Solutions1

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..

Solutions2

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 ResultItems { get; set; }

}

///

/// Method to Create an output parameter

///

/// The Parameter name

/// Type of the parameter

/// The value to set

///

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

}

}

}