调用从C#返回类型的Oracle函数

您实际上可以简化所有例程（程序、函数等）的铸造和准备参数，也可以使用这个小小的自动化简化ref游标类型的参数

a） 在通用包中定义以下类型和例程（让我们称之为utils）。

Type recRoutineSchema is Record (ColumnName varchar2(64),DataType Varchar2(20), ColumnOrder number, Direction varchar2(10), sSize nUMBER);
Type tblRoutineSchema is table of recRoutineSchema;
  function ftRoutineSchema(pkg varchar2,Routine varchar2) return  tblRoutineSchema  PIPELINED is 
      x recRoutineSchema;
       pkN  varchar2(100);
   rtN  varchar2(100);
   Begin 
     FOR Y in ( Select Argument_Name  ColumnName
                      ,Data_type      DataType
                      ,Position       ColumnOrder
                      ,In_out         Direction
                      ,Data_length    SSize 
                   from   user_ARGUMENTS 
                      where  package_Name=Upper(pkg) 
                         and object_name=Upper(Routine) order by position 
                         ) 
     LOOP
       PIPE ROW(Y);
     END LOOP;
     Return;  
   End;

b） 以及调用上述函数的c#方法，以检索和设置您正在调用的过程/函数的参数

public void SetupParams(string RoutineName, OracleCommand cmd, IDictionary<string, string> prms, bool keepConnectionOpen = true)
            {
            Debug.WriteLine("Setting parameters for " + RoutineName);
            if (cmd != null) cmd.Parameters.Clear();
            string pname = "";
            string[] s = RoutineName.Split('.');
            DataTable tblParams = Select(String.Format("Select * from Table(pkgUtils.ftRoutineSchema('{0}','{1}')) ", s[0], s[1]));
           cmd.CommandText=RoutineName; 
           foreach (DataRow dr in tblParams.Rows)
                {
                using (OracleParameter p = new OracleParameter())
                    {
                    pname = dr["COLUMnNAME"].ToString() == "" ? "returnvalue" : pname = dr["COLUMnNAME"].ToString().ToLower();
                    if (prms.Keys.Contains(pname)) p.Value = prms[pname];
                    string direction = dr["Direction"].ToString().ToLower();
                    string sptype = (string)dr["DataType"];
                    string[] sx = dr["DataType"].ToString().Split(new char[] { '(', ',', ')' });
                    direction = pname == "returnvalue" ? "rc" : direction;
                    p.ParameterName = pname;
                    #region case type switch
                    switch (sx[0].ToLower())
                        {
                        case "number":
                            // p.DbType = OracleDbType.Decimal;
                            p.OracleDbType = OracleDbType.Decimal;
                            break;
                        case "varchar2":
                            p.DbType = DbType.String;
                            p.Size = 65536;
                            //  p.Size = prms[pname].Length;
                            // p.Size = int.Parse(sx[1]);
                            break;
                        case "ref cursor":
                            p.OracleDbType = OracleDbType.RefCursor;
                            // direction = "rc"; // force return value
                            break;
                        case "datetime":
                            p.DbType = DbType.DateTime;
                            break;
                        case "ntext":
                        case "text":
                            p.DbType = DbType.String;
                            p.Size = 65536;
                            break;
                        default:
                            break;
                        }
                    //-------------------------------------------------------------------------------
                    switch (direction)
                        {
                        case "in": p.Direction = ParameterDirection.Input; break;
                        case "out": p.Direction = ParameterDirection.Output; break;
                        case "in/out": p.Direction = ParameterDirection.InputOutput; break;
                        case "rc": p.Direction = ParameterDirection.ReturnValue; break;
                        default: break;
                        }
                    #endregion
                    cmd.Parameters.Add(p); ;
                    }
                }
            } 

c） 。现在您可以很容易地调用任何函数/proc，如下所示：这个过程实际上返回了两个out-refcursor参数来populate数据集。

 private void btnDumpExcel_Click(object sender, EventArgs e)
         {
         IDictionary<string, string> p = new Dictionary<string, string>();
         p.Add("pcomno", "020");
         p.Add("pcpls", "221");
         p.Add("pUploaderName", "Anthony Peiris");
         try
            {
            pGroupDs = O.execProc2DatSet("priceWorx.prSnapshotDiscounts", p, false, false);
            Excel.MakeWorkBook(ref pGroupDs, ref O, "1");
            }
         catch (Exception ex)
            {
            Debug.WriteLine(ex);
            Debugger.Break();
            }
         //Excel.MakeWorkBook(ref ds, ref O, "1");
         }

这是O.execProc2DataSet 的方法

public DataSet execProc2DatSet(string storedProcedureName, IDictionary<string, string> prms, bool propagateDbInfo, bool leaveConnectionOpen = false)
            {
           // initPackage(storedProcedureName.Substring(0,storedProcedureName.IndexOf('.')));
            try
                {
                using (OracleCommand cmd = new OracleCommand("", conn))
                    {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = storedProcedureName;
                    //dep = new OracleDependency(cmd);
                    //dep.OnChange += new OnChangeEventHandler(dep_OnChange);
                    if (prms != null) SetupParams(storedProcedureName, cmd, prms, true);
                    using (OracleDataAdapter da = new OracleDataAdapter(cmd))
                        {
                        if (conn.State != ConnectionState.Open)
                            {
                            conn.Open();
                            cmd.Connection = conn;
                            }
                        using (DataSet ds = new DataSet())
                            {
                            da.Fill(ds);
                            return ds;
                            }
                        }
                    }
                }
            catch (Exception ex)
                {
                Debug.WriteLine(ex);
                Debugger.Break();
                return null;
                }
            finally
                {
                if (!leaveConnectionOpen) conn.Close();
                }
            }

这种方法允许您更改Proce/函数参数，而不必担心自上次以来可能更改了哪些参数，因为参数设置现在是完全自动的。HTH