public void TestFuncRefCur()
{
using (IDbConnection conn = new OraConnection())
{
conn.OpenConnection("chipanddale", "chipanddale", "xe");
IDbMgr dbManager = new OraDBMgr(conn, _LogMgr);
// function FuncRefCur(p_int32 in number, p_clob in out number, p_cur out sys_refcursor) return date;
IDbCommand command = new OraCommand("test_pkg.FuncRefCur");
command.CommandType = System.Data.CommandType.StoredProcedure;
OraParamDateTime returnParam = new OraParamDateTime("return", System.Data.ParameterDirection.ReturnValue, null);
command.AddDBParam(returnParam);
command.AddDBParam(new OraParamInt32("p_int32", System.Data.ParameterDirection.Input, null));
command.AddDBParam(new OraParamCLOB("p_clob", System.Data.ParameterDirection.InputOutput, "132"));
OraParamRefCursor refCur = new OraParamRefCursor("p_cur", System.Data.ParameterDirection.Output);
command.AddDBParam(refCur);
using (DbTransaction transaction = new DbTransaction(dbManager))
{
dbManager.Execute(command);
transaction.Success = true;
}
Debug.Assert(refCur.ParamValue.Rows.Count != 0, "p_cur");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[0] != null, "p_cur[0][0]");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[1] != null, "p_cur[0][1]");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[2] != null, "p_cur[0][2]");
Debug.Assert(refCur.ParamValue.Rows[0].ItemArray[3] != null, "p_cur[0][3]");
Debug.Assert(command.Params["p_clob"].GetValue() != null, "p_clob");
Debug.Assert(command.Params["return"].GetValue() != null, "return");
}
}
public string InsRequest(RequestEntity request)
{
_logger.Debug("InsRequest.");
if (request == null)
{
throw new ArgumentNullException("Request param can not be null.");
}
_logger.Debug("Params: request = {0};", request.ToInternalString());
OraCommand command = new OraCommand("REQ_PKG.INS_REQ");
command.CommandType = CommandType.StoredProcedure;
OraParamInt32 returnParam = new OraParamInt32("return", ParameterDirection.ReturnValue, null);
command.AddDBParam(returnParam);
command.AddDBParam(new OraParamDateTime("p_req_date", ParameterDirection.Input, request.ReqDateTime));
command.AddDBParam(new OraParamString("p_subject", ParameterDirection.Input, request.Subject));
command.AddDBParam(new OraParamString("p_note", ParameterDirection.Input, request.Comments));
command.AddDBParam(new OraParamInt32("p_orig_id", ParameterDirection.Input, request.Organization.NumId));
command.AddDBParam(new OraParamString("p_contact", ParameterDirection.Input, request.Contact));
command.AddDBParam(new OraParamInt32("p_req_type", ParameterDirection.Input, request.InfoSourceTypeId));
command.AddDBParam(new OraParamInt32("p_comp_id", ParameterDirection.Input, request.Application.NumId));
command.AddDBParam(new OraParamInt32("p_resp_id", ParameterDirection.Input, request.ResponseUser.NumId));
command.AddDBParam(new OraParamInt32("p_req_state", ParameterDirection.Input, request.StateId));
command.AddDBParam(new OraParamString("p_bug_num", ParameterDirection.Input, request.BugNumber));
command.AddDBParam(new OraParamInt32("p_CM_num", ParameterDirection.Input, string.IsNullOrEmpty(request.CMVersion)? (int?)null : Convert.ToInt32(request.CMVersion)));
command.AddDBParam(new OraParamString("p_ver_num", ParameterDirection.Input, request.ComponentVersion));
command.AddDBParam(new OraParamString("p_important", ParameterDirection.Input, request.IsImportantString));
OraParamDateTime createDateParam = new OraParamDateTime("p_create_date_out", ParameterDirection.Output, null);
command.AddDBParam(createDateParam);
Execute(command);
request.CreateDateTime = createDateParam.ParamValue.Value;
request.Id = returnParam.ParamValue.Value.ToString();
return(request.Id);
}