使用来自 .Net 的参数调用 Oracle 存储过程的正确 odbc 命令是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2687187/
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 is the proper odbc command for calling Oracle stored procedure with parameters from .Net?
提问by Hamish Grubijan
In the case of MSFT SQL Server 08, it is:
对于 MSFT SQL Server 08，它是：
odbcCommand = new OdbcCommand("{call " + SP_NAME + " (?,?,?,?,?,?,?) }", odbcConn);
When I try to do the same thing for Oracle, I get:
当我尝试为 Oracle 做同样的事情时，我得到：
OdbcException: ERROR [HYC00] [Oracle][ODBC]Optional feature not implemented.
Feel free to ask for clarification, and please help. I am using .Net 3.5, SQL Server 08, and Oracle 11g_home1.
随时要求澄清，并请帮助。我正在使用 .Net 3.5、SQL Server 08 和 Oracle 11g_home1。
P.S. The Oracle stored procedure does have some 3 more parameters, but I believe I am handling this in my code.
PS Oracle 存储过程确实有另外 3 个参数，但我相信我正在我的代码中处理这个。
回答by Vadim K.
I'm running .NET 3.5 and Oracle 10gR2. I've added an output parameter in response to your comment.
我正在运行 .NET 3.5 和 Oracle 10gR2。为了回应您的评论，我添加了一个输出参数。
Server, Uid, and Pwdhave been changed to protect the innocent. Set them to appropriate values.
Server, Uid, 并Pwd已更改以保护无辜者。将它们设置为适当的值。
My stored procedure:
我的存储过程：
create or replace
procedure test_proc(p1 in number, p2 in out varchar2) is
begin
p2 := to_char(p1 + to_number(p2));
end;
My test code:
我的测试代码：
using System;
using System.Data;
using System.Data.Odbc;
class OdbcTest
{
const string connectionString =
@"Driver={Microsoft ODBC for Oracle};" +
@"Server=MyTnsName;" +
@"Uid=MySchema;" +
@"Pwd=MyPassword;";
public static string TryMe()
{
using (var odbcConn = new OdbcConnection(connectionString))
using (var odbcCommand = odbcConn.CreateCommand())
{
odbcCommand.CommandText = "{ CALL test_proc(?, ?) }";
odbcCommand.CommandType = CommandType.StoredProcedure;
odbcCommand.Parameters.Add("p1", OdbcType.Decimal).Value = 42;
var p2 = odbcCommand.Parameters.Add("p2", OdbcType.VarChar, 30);
p2.Direction = ParameterDirection.InputOutput;
p2.Value = "25";
odbcConn.Open();
odbcCommand.ExecuteNonQuery();
return p2.Value as string; // returns 67
}
}
}
When using OUTor IN OUTparameters, the Sizeproperty of the OdbcParametermust be set to an adequate value.
使用OUT或IN OUT参数时，必须将的Size属性OdbcParameter设置为适当的值。
In response to your comment regarding exception handling, I would have the caller of the data access method handle exceptions. With the usingconstruct, the Disposemethod will be called automatically on the command and connection objects whether there is an exception or not.
为了回应您关于异常处理的评论，我会让数据访问方法的调用者处理异常。使用该using构造，Dispose无论是否有异常，都会在命令和连接对象上自动调用该方法。
