0

I'm using the flowing code to call the oracle function with the return value, but it returns null always

    OracleCommand cmd = new OracleCommand();
    using (OracleConnection cnn = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=321352427544)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=test)));User ID=abc;Password=123;"))
    {

        cmd.Connection = cnn;
        cmd.CommandText = "GetEmp";
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("P_EMP_ID", OracleDbType.Int32).Value = 4241;
    

        cmd.Parameters.Add(new OracleParameter("return_value", OracleDbType.Int32)).Direction = ParameterDirection.ReturnValue;

        cnn.Open();
        cmd.ExecuteNonQuery();

        string Count = (string)cmd.Parameters["return_value"].Value;

        cnn.Close();
    }
jps
  • 20,041
  • 15
  • 75
  • 79
Nebro Prog
  • 65
  • 1
  • 7
  • Show the `GetEmp` function – Tim Schmelter Sep 18 '22 at 10:03
  • it's very big, but when I called it from oracle it worked properly – Nebro Prog Sep 18 '22 at 10:05
  • You are missing the `@` character. See : https://stackoverflow.com/questions/11048910/oraclecommand-sql-parameters-binding – jdweng Sep 18 '22 at 10:06
  • @jdweng: it's oracle not sql-server, are you sure? If you look at your link, they also don't use `@` – Tim Schmelter Sep 18 '22 at 10:09
  • I think we shouldn't use @ – Nebro Prog Sep 18 '22 at 10:14
  • 1
    Why a stored-procedure `GetEmp` needs to be called with `ExecuteNonQuery`, what does it do? – Tim Schmelter Sep 18 '22 at 10:20
  • 1
    GetEmp is a function not a Stored Procedure – Nebro Prog Sep 18 '22 at 10:23
  • 2
    You are defining that parameter as type Int32, so why the cast to string? Try casting to int instead – Hans Kesting Sep 18 '22 at 10:38
  • I'm not used to oracle anymore, i doubt that [this answer](https://stackoverflow.com/a/35843623/284240) is right but it's worth a try: add the return value parameter first(doesn't make sense to me though). And yes, why you cast to string if it is an integer? Maybe you should really return an int or change the type of the parameter to `OracleDbType.Varchar2`. – Tim Schmelter Sep 18 '22 at 10:52
  • @TimSchmelter : Its a colon instead of an `@` – jdweng Sep 18 '22 at 11:10
  • thank you @tim-schmelter the problem is solved by adding cmd.BindByName = true; – Nebro Prog Sep 18 '22 at 11:28
  • @NebroProg: oracle is strange. So it would have also been fixed by adding the return value parameter first as i said? – Tim Schmelter Sep 18 '22 at 11:30
  • mmm actually yeh it also solves the problem but rather than string Count = (string)cmd.Parameters["return_value"].Value; should be string Count = (string)cmd.Parameters["return_value"].Value.ToString(); because this error is happened System.InvalidCastException: 'Unable to cast object of type 'Oracle.ManagedDataAccess.Types.OracleString' to type 'System.String'.' – Nebro Prog Sep 18 '22 at 11:39
  • @NebroProg: then edit your answer to include the information that the wrong order was the reason and there are 2 ways to fix it: 1. Add the return-value-parameter first or 2. Use `BindByName = true`. The `InvalidCastException` is a different issue which you could have mentioned in your question. The reason is that an `OracleString` is not a `String`, so you have to use [`OracleString.ToString`](https://learn.microsoft.com/en-us/dotnet/api/system.data.oracleclient.oraclestring.tostring?view=netframework-4.8)(as you did). However, that raises the question why you return count as string at all. – Tim Schmelter Sep 18 '22 at 12:18

1 Answers1

0

the problem was solved in two ways by

  1. add cmd.BindByName = true;
  2. add the return parameter in the first: cmd.Parameters.Add(new OracleParameter("return_value", OracleDbType.Int32)).Direction = ParameterDirection.ReturnValue;
Nebro Prog
  • 65
  • 1
  • 7
  • BRILLIANT ! Thank you so much! I have been searching all over the web and trying out so many rubbish examples... all I needed was that BindByName. Thank you ! For anyone else who is trying to return a Varchar2 then you will need to know that you must explicitly set the size of the return parameter AND it is specified in bytes not chars so make it plenty big. eg: cmd.Parameters.Add("retVal", OracleDbType.Varchar2, 100).Direction = ParameterDirection.ReturnValue; the size there is 100 bytes... – Vida Mar 31 '23 at 13:10