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

Question

我正在使用 C# 尝试调用 Oracle 程序包中返回类型的函数。

过去几天我一直在研究这个问题，到目前为止我得到的建议是:

• 使用 odp.net 11g 数据访问驱动程序。
• 确保参数输出方向设置为返回值
• 确保参数输出是第一个添加的参数
• 为输出参数提供一个 udttypename，它是 Oracle 类型姓名。
• 确保这个 udttypename 是大写的(她的一些类似案例已通过此解决)

下面是Oracle包(包叫prefs):

Type P_Details Is Record(
   var1    a.a_Type_Key%Type
  ,var2    Varchar2(1)
  ,var3    a.b%Type
  ,var4    c.Type_Key%Type
  ,var5    d.Code%Type
  ,var6    d.Product_Path%Type
  ,var7    a.Channel_Key%Type
  ,var8    a.From_Date%Type
  ,var9    a.To_Date%Type);

Type P_List Is Table Details;

Function Get(p_1   In     Number,
             p_2   In     Varchar2,
             p_3   In     Varchar2,
             p_4   In     Date,
             p_5   In Out Varchar2) Return List;

下面是调用Oracle包的C#代码。

using (var connection = new OracleConnection(ConnectionString))
{
    using (var command = new OracleCommand
                             {
                                 CommandType = CommandType.StoredProcedure,
                                 CommandText = "PACKAGENAME.FUNCTIONNAME",
                                 Connection = connection,
                                 BindByName = true
                             })
    {
        var output = new OracleParameter
                         {
                             UdtTypeName = "PREFS.PREFERENCE_LIST",
                             ParameterName = "p_details",
                             OracleDbType = OracleDbType.Object,
                             Direction = ParameterDirection.ReturnValue
                         };
        command.Parameters.Add(output);
        command.Parameters.Add(new OracleParameter
                                   {
                                       ParameterName = "p_1",
                                       OracleDbType = OracleDbType.Decimal,
                                       Direction = ParameterDirection.Input,
                                       Value = details.RuleId
                                   });
        command.Parameters.Add(new OracleParameter
                                   {
                                       ParameterName = "p_2",
                                       OracleDbType = OracleDbType.Decimal,
                                       Direction = ParameterDirection.Input,
                                       Value = details.CustomerDetails.CtiId
                                   });
        command.Parameters.Add(new OracleParameter
                                   {
                                       ParameterName = "p_3",
                                       OracleDbType = OracleDbType.Varchar2,
                                       Direction = ParameterDirection.Input,
                                       Value = details.CustomerDetails.Surname
                                   });
        command.Parameters.Add(new OracleParameter
                                   {
                                       ParameterName = "p_4",
                                       OracleDbType = OracleDbType.Varchar2,
                                       Direction = ParameterDirection.Input,
                                       Value = details.CustomerDetails.Postcode
                                   });
        command.Parameters.Add(new OracleParameter
                                   {
                                       ParameterName = "p_5",
                                       OracleDbType = OracleDbType.Date,
                                       Direction = ParameterDirection.Input,
                                       Value = details.CustomerDetails.DateOfBirth
                                   });
        command.Parameters.Add(new OracleParameter
                                   {
                                       ParameterName = "p_6",
                                       OracleDbType = OracleDbType.Varchar2,
                                       Direction = ParameterDirection.InputOutput
                                   });
        connection.Open();
        command.ExecuteNonQuery();
    }
}

我现在遇到一个错误

"OCI-22303: type "PACKAGENAME.TYPENAME" not found"

对于 UDTTYPENAME，我尝试了以下格式

• 类型名
• 函数名称.类型名称
• PACKAGENAME.TYPENAME
• PACKAGENAME.TYPENAME
• PACKAGENAME.FUNCTIONNAME.TYPENAME
• SCHEMA.PACKAGENAME.TYPENAME

我很感激对此的任何帮助和回应，因为我现在已经没有想法了。

Answer 1

实际上，您可以简化所有例程(过程、函数等)的转换和准备参数，以及使用这种小自动化的 ref cursor 类型的参数

a) 在公共(public)包中定义以下类型和例程(我们称之为 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).现在您可以轻松调用任何函数/过程，如下所示，此过程实际上返回两个输出引用参数来填充数据集。

 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();
                }
            }

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