在尝试使用这个 oracle 包/过程时我做错了什么？

Question

我已经编写了这个包，

CREATE OR REPLACE 
PACKAGE CURVES AS 
  type t_forecast_values is table of test.column2%TYPE index by varchar(20);
  assoc_array t_forecast_values;
  procedure sp_insert (param1 in varchar2, param2 in number);
END CURVES;

create or replace
package body curves as
  procedure sp_insert (param1 in varchar2, param2 in number) as
  begin
    assoc_array(param1) := param2;
    DECLARE primarykey NUMBER(10);
    BEGIN
      FOR i IN 1..2
      LOOP
        SELECT seq_curves.nextval INTO primarykey FROM dual;
        INSERT INTO TEST (column1, column2, column3)
        VALUES (primarykey, param1, assoc_array(param1));

        INSERT INTO TEST2 (column1, column2)
        VALUES (primarykey, 'default');
      END LOOP ;
    END;
  end sp_insert;
end curves;

我通过此 c# 代码调用该过程：

class Program
    {
        private static string connString = @"User Id=User; Password=root; Data Source=SOURCE";

        private static List<string> forecast_types = new List<string> { "a", "b" };
        private static List<double> forecast_values = new List<double> { .1, .2 };

        static void Main(string[] args)
        {
            using (var con = new OracleConnection(connString))
            {
                string query = "BEGIN curves.sp_insert(:forecast_types, :forecast_values); END;";

                try
                {
                    con.Open();

                    using (OracleCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = query;
                        cmd.CommandType = CommandType.Text;
                        cmd.BindByName = true;

                        cmd.Parameters.Add(new OracleParameter
                            {
                                ParameterName = ":forecast_types",
                                OracleDbType = OracleDbType.Varchar2,
                                Value = forecast_types.ToArray(),
                                Direction = ParameterDirection.Input,
                                CollectionType = OracleCollectionType.PLSQLAssociativeArray
                            }
                        );
                        cmd.Parameters.Add(new OracleParameter
                        {
                            ParameterName = ":forecast_values",
                            OracleDbType = OracleDbType.Double,
                            Value = forecast_values.ToArray(),
                            Direction = ParameterDirection.Input,
                            CollectionType = OracleCollectionType.PLSQLAssociativeArray
                        }
                        );

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (System.Exception ex)
                {
                    System.Console.WriteLine(ex);
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }

我收到以下错误：

Oracle.DataAccess.Client.OracleException ORA-06550: line 1, column 7: 
PLS-00306: wrong number or types of arguments in call to 'SP_INSERT'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_INSERT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored at     Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
   at OracleTest.Program.Main(String[] args) in \\comp\user$\Visual Studio 2010\Projects\OracleTest\OracleTest\Program.cs:line 57

我还不知道如何解决该问题。任何帮助表示赞赏。

Answer 1

您的包不接受 PLSQL 关联数组，而是接受单个元素。我相信，您想要做的是进行数组插入（这几乎是将您的代码打包在 C# 中 X 次，然后将其批量发送到数据库来完成工作）

此链接直接描述了您想要执行的操作：
http://www.oracle.com/technology /sample_code/tech/windows/odpnet/howto/arraybind/index.html

（我对您的代码进行了一些更改，即

1. 删除了 pl/sql assoc.array para 类型
2. 添加了 ArrayBindCount
3. 您使用的是匿名块，将其更改为存储过程调用

1 和 2 应该可以解决您的问题，而 #3 更多的是语法糖，

但是，此代码未经测试但可以工作

class Program
    {
        private static string connString = @"User Id=User; Password=root; Data Source=SOURCE";

        private static List<string> forecast_types = new List<string> { "a", "b" };
        private static List<double> forecast_values = new List<double> { .1, .2 };

        static void Main(string[] args)
        {
            using (var con = new OracleConnection(connString))
            {
                //string query = "BEGIN curves.sp_insert(:forecast_types, :forecast_values); END;";
                string query = "curves.sp_insert";

                try
                {
                    con.Open();

                    using (OracleCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = query;
                        cmd.CommandType = CommandType.StoredProcedure; // CommandType.Text; //you can change this to a stored procedure and not utilize the anonymous block

                        cmd.ArrayBindCount = 2; //use ArrayBindCount

                        cmd.BindByName = true;

                     cmd.Parameters.Add(new OracleParameter
                        {
                            ParameterName = ":forecast_types",
                            OracleDbType = OracleDbType.Varchar2,
                            Value = forecast_types.ToArray(),
                            Direction = ParameterDirection.Input,
                            //CollectionType = OracleCollectionType.PLSQLAssociativeArray //this is not needed
                        }
                    );
                    cmd.Parameters.Add(new OracleParameter
                    {
                        ParameterName = ":forecast_values",
                        OracleDbType = OracleDbType.Double,
                        Value = forecast_values.ToArray(),
                        Direction = ParameterDirection.Input,
                        //CollectionType = OracleCollectionType.PLSQLAssociativeArray  //this is not needed
                    }
                        );

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (System.Exception ex)
                {
                    System.Console.WriteLine(ex);
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }

---

编辑

---

我确实注意到您这样做了尝试传递一个关联数组（我看到在过程中创建了循环类型），

这也不是太难。
从这一点来看，你走在正确的轨道上。

您需要更改软件包才能拥有 assoc。通过 params 传入的数组
创建或替换
封装曲线为
type t_forecast_values 是 test.column2%TYPE 的表，由 varchar(20) 索引；
assoc_array t_forecast_values；

  --need to create an assoc.array for numbers
  type t_numbera_values is table of test.column2%TYPE index by number(10);

  --changed to accept the assoc.array to varchar and numbers
  procedure sp_insert (param1 in t_forecast_values, param2 in t_numbera_values);
END CURVES;

create or replace
PACKAGE BODY CURVES AS
  --changed to accept the assoc.array to varchar and numbers
  PROCEDURE SP_INSERT (PARAM1 IN T_FORECAST_VALUES, PARAM2 IN T_NUMBERA_VALUES) AS
  BEGIN
    --assoc_array(param1) := param2; /*not sure what the intent of this was...*/
    DECLARE PRIMARYKEY NUMBER(10);
    BEGIN
      FOR i IN PARAM1.first .. PARAM1.last
      LOOP
        SELECT seq_curves.nextval INTO primarykey FROM dual;
        INSERT INTO TEST (COLUMN1, COLUMN2, COLUMN3)
        VALUES (primarykey, PARAM1(i), PARAM2(i));

        INSERT INTO TEST2 (column1, column2)
        VALUES (PRIMARYKEY, 'default');
      END LOOP ;
      --assuming you are going to add error handling here
    END;
  end sp_insert;
END CURVES;

并且您需要将大小参数添加到参数列表中，

class Program
    {
        private static string connString = @"User Id=User; Password=root; Data Source=SOURCE";

        private static List<string> forecast_types = new List<string> { "a", "b" };
        private static List<double> forecast_values = new List<double> { .1, .2 };

        static void Main(string[] args)
        {
            using (var con = new OracleConnection(connString))
            {
                string query = "curves.sp_insert";

                try
                {
                    con.Open();

                    using (OracleCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText = query;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.BindByName = true;

                        cmd.Parameters.Add(new OracleParameter
                            {
                                ParameterName = "param1",
                                OracleDbType = OracleDbType.Varchar2,
                                Value = forecast_types.ToArray(),
                                Direction = ParameterDirection.Input,
                                CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                                Size = 2
                            }
                        );
                        cmd.Parameters.Add(new OracleParameter
                        {
                            ParameterName = "param2",
                            OracleDbType = OracleDbType.Double,
                            Value = forecast_values.ToArray(),
                            Direction = ParameterDirection.Input,
                            CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                            Size = 2                            
                        }
                        );

                        cmd.ExecuteNonQuery();
                    }
                }
                catch (System.Exception ex)
                {
                    System.Console.WriteLine(ex);
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }

以获得此 goto 的一个很好的示例
ORACLE_HOME\odp.net\samples\2.x\AssocArray\AssocArray.cs
它完全按照您的要求执行

以上所有代码都未经测试，但应该让您走上正确的轨道，无论是从数组绑定（第一个示例）还是传入 pl/sql 数组（第二个示例）

Answer 2

不确定这是否是问题所在，但我相信 OracleDbType.Double 将映射到数据库类型 FLOAT，而不是声明的 NUMBER第二个参数的类型。我认为您应该将第二个参数的类型设置为 此表。