我在尝试使用这个 oracle 包/过程时做错了什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4759072/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What am i doing wrong while trying to use this oracle package/procedure?
提问by mrt181
I have written this package
我已经写了这个包
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;
I call the procdedure through this c# code:
我通过这个 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();
}
}
}
}
i get the following error:
我收到以下错误：
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
I have no idea to resolve the problem, yet. Any help is appreciated.
我还不知道要解决这个问题。任何帮助表示赞赏。
回答by Harrison
Your package is not accepting a PLSQL Associative array, but rather single elements. What you are trying to do, I believe, is do an array insert (which is pretty much packaging up in C# your code X times and bulk sending it to the database to do the work)
您的包不接受 PLSQL 关联数组，而是接受单个元素。我相信你想做的是做一个数组插入（这几乎是在 C# 中打包你的代码 X 次并将它批量发送到数据库来完成工作）
this link directly describes what you want to do: http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/arraybind/index.html
这个链接直接描述了你想要做什么：http: //www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/arraybind/index.html
(I made a few changes to your code, namely
（我对您的代码进行了一些更改，即
- removed the pl/sql assoc. array para type
- added ArrayBindCount
- You were using an anonymous block, changed this to a stored procedure call
- 删除了 pl/sql 关联。数组参数类型
- 添加了 ArrayBindCount
- 您正在使用匿名块，将其更改为存储过程调用
1 & 2 should fix your problem, while #3 is more of syntactic sugar
1 & 2 应该可以解决您的问题，而 #3 则更多是语法糖
however, this code is untested but out to work
但是，此代码未经测试但可以正常工作
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();
}
}
}
}
EDIT
编辑
I did notice that you did tryto pass in an associative array (I see the creation of the type of the looping within the procedure).
我确实注意到您确实尝试传入关联数组（我看到在过程中创建了循环类型）。
This is not too difficult to do either. On that note you are on the correct track.
这也不是很难做到。在那个音符上，你是在正确的轨道上。
you'll need to change the package to have assoc. arrays passed in via params 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;
您需要更改包以具有关联。通过 params CREATE OR REPLACE PACKAGE CURVES 传入的数组作为类型 t_forecast_values 是由 varchar(20) 生成的 test.column2%TYPE 索引表；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;
and you will need to add a size param to the param list as such
并且您需要将一个大小参数添加到参数列表中
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();
}
}
}
}
to get a great example of this goto ORACLE_HOME\odp.net\samples\2.x\AssocArray\AssocArray.cs it does exactly what you like
要获得一个很好的例子，请转到 ORACLE_HOME\odp.net\samples\2.x\AssocArray\AssocArray.cs 它完全符合您的喜好
all of the above code is untested but should get you on the correct track, either from array binding (first example) or passing in the pl/sql array (second example)
以上所有代码都未经测试，但应该让您走上正确的轨道，无论是从数组绑定（第一个示例）还是传入 pl/sql 数组（第二个示例）
回答by Dave Costa
Not sure if this is the problem, but I believe the OracleDbType.Doublewould map to the database type FLOAT, not NUMBERwhich is the declared type of the second parameter. I think you should be setting the type of the second parameter to OracleDbType.Decimalper this table.
不确定这是否是问题，但我相信OracleDbType.Double会映射到数据库类型FLOAT，而不是NUMBER第二个参数的声明类型。我认为您应该将第二个参数的类型设置为OracleDbType.Decimal每个表。
