如何从.NET中的存储过程返回oracle输出参数

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上执行它可以正常工作。

2 个解决方案

#1

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)

#2

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 OUT parameter 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 OUT parameter, and get the value out, of the stored procedure.

显然,这会遗漏所有其他正在发送的参数以及其他“out”参数 - 希望简化它。但这显示了如何在C#中调用存储过程之前,期间和之后设置所有内容,以及如何设置存储过程的OUT参数并获取值。

#1