如何通过C#将数组作为参数调用Oracle存储过程? [英] How to call Oracle stored procedure with array as parameter by C# ?
问题描述
嗨〜
我想通过C#将数组作为参数调用Oracle存储过程。
我搜索了所有可能的问题〜
程序可以在Oracle中编译,但不能从C#调用。
CREATE 或 REPLACE PACKAGE pk
AS
TYPE VAY IS ARRAY( 11 ) OF VARCHAR2( 30 );
PROCEDURE OrderReport(V_List IN VAY);
END
CREATE OR 更换包裹pk AS
< pre lang = sql> PROCEDURE OrderReport(V_List IN VAY) AS
BEGIN
INSERT INTO T020(A,B,C,D,E,F,G,H,I) VALUES (V_List( 0 ),V_List( 1 ),V_List( 2 ), V_List( 3 ),V_List( 4 ),V_List( 5 ),V_List( 6 ),V_List( 7 ),to_timestamp(V_List(< span class =code-digit > 8 ),' yyyy-mm-dd hh24:mi:ss.ff3'));
END ;
END ;
以下代码不能工作,错误消息是无效参数
<前lang =c#> 私人 static void OrderReport()
{
string [] _List =
{
,
B,
C,
D ,
E,
F,=
G,
< span class =code-string> H,
102.2,
0,
DateTime.Now.ToString( yyyy-MM-dd HH:mm:ss.fff)
};
字符串 ConnectionString = ConfigurationManager.ConnectionStrings [ ABC跨度>]的ConnectionString。
使用(OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
使用(OracleCommand cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = PK.ORDERREPORT;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add( V_List,OracleDbType.Array);
cmd.Parameters [ 0 ]。Value = _List;
cmd.ExecuteNonQuery();
}
catch (例外情况)
{
Console.WriteLine(ex.Message);
}
Console.WriteLine( 1);
Console.ReadLine();
}
}
} 我得到答案
ORACLE TYPE ---
TYPETESTVARRAYAS VARRAY(3000)VARCHAR(30)
ORACLE STORED PROCEDURE ---
程序测试(TESTVARRAY中的V_List)AS
BEGIN
插入AAAA(A,B,C,D,E,F,G,H,DATETIME)值(V_List(1),V_List(2) ),V_List(3),V_List(4),V_List(5),V_List(6),V_List(7),V_List(8),to_timestamp(V_List(9),''yyyy-mm-dd hh24:mi: ss.ff3 ''));
END;< / pre>
Hi~
I wanna call Oracle stored procedure with array as parameters by C#.
I have searched all possible question~
The procedure can be compiled in Oracle,but can''t be call from C#.
CREATE OR REPLACE PACKAGE pk
AS
TYPE VAY IS ARRAY(11)OF VARCHAR2(30);
PROCEDURE OrderReport(V_List IN VAY);
END
CREATE OR REPLACE PACKAGE BODY pk AS
<pre lang="sql">PROCEDURE OrderReport(V_List IN VAY) AS
BEGIN
INSERT INTO T020 (A, B,C, D, E, F, G, H, I) VALUES(V_List(0), V_List(1), V_List(2), V_List(3), V_List(4), V_List(5), V_List(6), V_List(7), to_timestamp(V_List(8), 'yyyy-mm-dd hh24:mi:ss.ff3'));
END;
END;
The following codes can''t work, the error message were "Invalid parameter"
private static void OrderReport()
{
string[] _List =
{
"A",
"B",
"C",
"D",
"E",
"F",=
"G",
"H",
"102.2",
"0",
DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")
};
String ConnectionString = ConfigurationManager.ConnectionStrings["ABC"].ConnectionString;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
conn.Open();
using (OracleCommand cmd = conn.CreateCommand())
{
try
{
cmd.CommandText="PK.ORDERREPORT";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("V_List",OracleDbType.Array);
cmd.Parameters[0].Value = _List;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.WriteLine("1");
Console.ReadLine();
}
}
}
I Got Answer
ORACLE TYPE--- TYPE "TESTVARRAY" AS VARRAY(3000) OF VARCHAR(30) ORACLE STORED PROCEDURE--- PROCEDURE TEST(V_List IN TESTVARRAY) AS BEGIN INSERT INTO AAAA (A, B,C, D, E, F, G, H, DATETIME) VALUES(V_List(1), V_List(2), V_List(3), V_List(4), V_List(5), V_List(6), V_List(7), V_List(8), to_timestamp(V_List(9), ''yyyy-mm-dd hh24:mi:ss.ff3'')); END;</pre>
这篇关于如何通过C#将数组作为参数调用Oracle存储过程?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!