How to return oracle output parameters from stored procedure in .NET.

Question

I'm having serious problems trying to return data from SP. I tried to do it like this:

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 does not return anything to the variables sucess or errorMessage. What am I doing wrong? Is there a better way? It works great when executed directly on Oracle.

Answer 1

It seems you cannot use an existing variable as an output parameter, try instead

 ora_cmd.Parameters.Add("Lc_Exito", OracleDbType.Int32).Direction = ParameterDirection.Output; ora_cmd.ExecuteNonQuery(); if (ora_cmd.Parameters["Lc_Exito"].value == 0) 

Answer 2

I have not found anywhere where he documents the whole process in one place, therefore, having hit my head against the wall and hit it, here is my version of what I came up with using one of the output parameters from the OP Code:

 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 exact case 

Then you need to configure the stored procedure to accept the OUT parameter, and you must assign a procedure to it:

 create or replace procedure PR_ABC_P_ALTA_TARJETA_PAYWARE(Lc_Exito OUT number) as begin Lc_Exito := 123; end; / 

Obviously, this excludes all other parameters that were sent, and other parameters “outside” - they wanted to simplify it. But this shows how everything is configured, from before, during and after calling the stored procedure in C #, and how to set the OUT parameter and get the value outside the stored procedure.