如何在MVC控制器中接受datatable out参数

Question

在我的控制器中，我调用了一个具有以下结构的oracle过程：

    pbm_pkg.get_member(
      p_id               IN VARCHAR2,
      p_age              IN NUMBER,
      p_client           IN NUMBER,
      p_date             IN DATE,
      p_tob              OUT tob_type) ;

taking 4 input parameter and 1 output parameter. The output parameter is a table having 10 columns

My controller code

using (OracleConnection con = AppConn.Connection)
{
 OracleCommand cmd = con.CreateCommand();
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Connection = con;

cmd.CommandText = "pbm_pkg.get_member";

OracleParameter p_id = new OracleParameter();
p_id.OracleDbType = OracleDbType.Varchar2;
p_id.Direction = ParameterDirection.Input;
p_id.Value = id;

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

 //same assignment for client and date
OracleParameter p_tob = new OracleParameter();
p_tob.OracleDbType =//**what will the oracle db type here ??**
p_tob.Direction = ParameterDirection.Output;

cmd.Parameters.Add(p_id);
cmd.Parameters.Add(p_age);
cmd.Parameters.Add(p_client);
cmd.Parameters.Add(p_date);
cmd.Parameters.Add(p_tob);

con.Open();
cmd.ExecuteNonQuery();
{
**//How to read the output parameter of type table and show it in view?**
}

我有两个疑问：

1. 对于oracle表类型的输出参数，oracle类型是什么类型。
2. 如何在执行命令后读取输出参数中的数据。

编辑的

TYPE pulse_integration.tob_bft_rec_type AS OBJECT (
  Benefit_Id           Number(14),
  Benefit_Name         Varchar2(500),
  Covered              Number(1),
  Area_Id              Number(14),
  Limit_Amount         Number(14, 4),
  Family_Limit         Number(14, 4),
  Deductable_Amount    Number(14, 4),
  Co_Pay               Number(6, 4),
  Co_Pay_Min           Number(14, 4),
  Co_Pay_Max           Number(14, 4),
  Co_Pay_Otn           Number(6, 4),
  Co_Pay_Outside       Number(6, 4),
  Co_Pay_Otn_Outside   Number(6, 4),
  Need_Approval_Amount Number(14),
  Sessions_No          Number(4),
  Waiting_Period       Number,
  Mapped_Benefit_Id    Number(14))
/

TYPE pulse_integration.tob_tab_type AS TABLE OF pulse_integration.tob_bft_rec_type
/

Answer 1

也许您可以避免这种情况，只需编写pl/sql就可以了，它调用这个package.proc()打开回游标，然后迭代结果？

这是一个psudo代码

cmd.CommandText = "declare
                        v_id                VARCHAR2;
                        v_age               NUMBER;
                        v_client            NUMBER;
                        v_date              DATE;
                        v_tob_typ tob_typ; -- whereever it comes from 
                    begin
                        pbm_pkg.get_member(v_id, v_age,v_client,v_date, v_tob);
                        open :refcur for select tob_typ.col1, tob_typ.col2 from dual;
                    end;";

OracleParameter p = cmd.Parameters.Add(
                      "rs",
                       OracleDbType.RefCursor,
                       DBNull.Value,
                       ParameterDirection.Output
                        );

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

看看这个能不能用！