C# 如何从 .NET 中的存储过程返回 oracle 输出参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14247921/
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
How to return oracle output parameters from a stored procedure in .NET
提问by Jesus Zamora
I am having serious issues trying to get the data back from the SP. I was trying to do it like this:
我在尝试从 SP 取回数据时遇到严重问题。我试图这样做：
OracleCommand ora_cmd = new OracleCommand("a6r1.PR_ABC_P_ALTA_TARJETA_PAYWARE", ora_conn);
ora_cmd.BindByName = true;
ora_cmd.CommandType = CommandType.StoredProcedure;
int success= new int();
ora_cmd.Parameters.Add("Lc_Param_Issuer", OracleDbType.Varchar2, issuer, ParameterDirection.Input);
ora_cmd.Parameters.Add("Ln_Param_Valid_Product", OracleDbType.Varchar2, DropDownListProducto.SelectedValue.ToString(), ParameterDirection.Input);
ora_cmd.Parameters.Add("Ln_Param_Total", OracleDbType.Int32, parsed, ParameterDirection.Input);
ora_cmd.Parameters.Add("Lc_Param_User", OracleDbType.Varchar2, user, ParameterDirection.Input);
ora_cmd.Parameters.Add("Lc_Encrypted_Password", OracleDbType.Varchar2, pass, ParameterDirection.Input);
ora_cmd.Parameters.Add("Lc_Exito", OracleDbType.Int32, success, ParameterDirection.Output);
ora_cmd.Parameters.Add("Lc_Error", OracleDbType.Varchar2, errorMessage, ParameterDirection.Output);
But it is not returning anything to the variables sucess or errorMessage. What am I doing wrong? Is there a better way? It works fine when executed directly on Oracle.
但它不会向变量 sucess 或 errorMessage 返回任何内容。我究竟做错了什么？有没有更好的办法？直接在 Oracle 上执行时它工作正常。
采纳答案by uowzd01
It seems you cannot use existing variable as output parameter, try this way instead
似乎您不能使用现有变量作为输出参数，请尝试这种方式
ora_cmd.Parameters.Add("Lc_Exito", OracleDbType.Int32).Direction = ParameterDirection.Output;
ora_cmd.ExecuteNonQuery();
if (ora_cmd.Parameters["Lc_Exito"].value == 0)
回答by vapcguy
I have not found anywhere where it documents the whole process in one place, so after hitting my head against the wall and banging it out, here is my version of what I came up with, using one of the output parameters from the OP's code:
我还没有找到将整个过程记录在一个地方的任何地方，因此在将我的头撞到墙上并将其敲打之后，这是我使用 OP 代码中的输出参数之一提出的版本：
OracleParameter param = new OracleParameter();
param = ora_cmd.Parameters.Add("Lc_Exito", OracleDbType.Int32, ParameterDirection.Output); // can assign the direction within the parameter declaration
param.Size = 25; // failed for me if I did not have this - should be the same as the DB field, if exporting a value from the database
ora_cmd.ExecuteNonQuery();
int myLc_ExitoValue = int.Parse(param.Value); // might not need to parse, and might need a .ToString() on param.Value if you do - I was using strings so not sure about OP's exact case
Then the stored procedure needs to be set up to accept the OUTparameter and you must assign to it in the procedure:
然后需要设置存储过程以接受OUT参数，并且必须在过程中分配给它：
create or replace procedure PR_ABC_P_ALTA_TARJETA_PAYWARE(Lc_Exito OUT number)
as
begin
Lc_Exito := 123;
end;
/
Obviously this leaves out all the other parameters that were being sent in and the other "out" parameters - wanted to simplify it. But this shows how everything gets set up, from before, during, and after the call to the stored procedure in the C#, and how to set the OUTparameter, and get the value out, of the stored procedure.
显然，这遗漏了所有其他正在发送的参数和其他“输出”参数 - 想要简化它。但这显示了从调用 C# 中的存储过程之前、期间和之后如何设置所有内容，以及如何设置OUT参数和获取存储过程的值。
