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

Question

I am having serious issues trying to get the data back from the SP. I was trying 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 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.

uowzd01 · Accepted Answer · 2014-06-17 23:53:37Z

21

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)

edited Jun 17, 2014 at 23:53

answered Jan 9, 2013 at 23:22

uowzd01

1,00010 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jesus Zamora Over a year ago

Yes, you are absolutely right. I did not expect oracle parameters to be so specific versus sql's. Than you very much.

S. Dixon Over a year ago

if (ora_cmd.Parameters["Lc_Exito"]).value == 0) has an extra bracket in it. Do you mean if (ora_cmd.Parameters["Lc_Exito"].value == 0)?

vapcguy · Accepted Answer · 2016-10-03 18:36:34Z

9

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:

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:

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.

edited Oct 3, 2016 at 18:36

answered Oct 3, 2016 at 18:00

vapcguy

1

1 Comment

Mike Rod Over a year ago

I would like to highlight that the "Size" property is very important for the string output parameters, as the inline comment in the code indicates.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related