Stored procedure not returning value

Question

I'm relatively new to both, c# and Oracle. I'm having a problem with Oracle stored procedure not returning a value back to my c# code. I've spent several days looking for a solution, trying everything I could find about this topic on the web. And I still cannot make this work, so I would really appreciate any help I can get.

I'm posting current code I have (oracle stored procedure and c# code, respectively). This current solution does not return any error, but the problem is that stored procedure does not return any result ('dr.HasRows' in my c# code returns false). I've checked my stored procedure on Oracle (without OUT parameter) and it works as it should, meaning, it returns correct value. Also, my c# code works fine if I use in-lay SQL statement, instead of procedure.

Here is my Oracle stored procedure:

CREATE OR REPLACE PROCEDURE P_TIP_PODJETJA
(
   tip in number,
   o_sco out number
)
AS
   sco number;
BEGIN
   select score into sco
   from sco_sif_score a
   where a.sif_kat = 3
   and a.tip_pod = tip;
   o_sco:= sco;
END P_TIP_PODJETJA;

And here is my c# code:

using (OracleCommand cmd = new OracleCommand())
{
  cmd.Connection = conn;                    
  cmd.CommandText = "P_TIP_PODJETJA";
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.Add(new OracleParameter("tip", Podjetje.TipSub));
  cmd.Parameters.Add(new OracleParameter("o_sco", OracleDbType.Decimal, ParameterDirection.Output));
  cmd.BindByName = true;
  using (OracleDataReader dr = cmd.ExecuteReader())
  {
    if (dr.HasRows)
    {
      dr.Read();
      Score.ScoTipPodjetja = dr.GetDecimal(0);
    }
  }

}

Answer 1

Thank you all for helping. With your help and some additional research I manage to make this work. I've actually created both solution, with using stored procedure and function (thanks to Frederick Alvarez), respectively. Just in case, anyone else would have similar issue in future.

1. Using Oracle Stored Procedure

a. Oracle Stored Procedure:

create or replace PROCEDURE P_TIP_PODJETJA
(
    tip in number,
    o_sco out number
)
AS
    sco number;
BEGIN
    select score into sco
    from sco_sif_score a
    where a.sif_kat = 3
    and a.tip_pod = tip;
    o_sco:= sco;
END P_TIP_PODJETJA;

b. C# Code:

using (OracleCommand cmd = new OracleCommand())
{
    cmd.Connection = conn;

    cmd.CommandText = "P_TIP_PODJETJA";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new OracleParameter("tip", Podjetje.TipSub));
    cmd.Parameters.Add(new OracleParameter("o_sco", OracleDbType.Decimal, ParameterDirection.Output));
    cmd.BindByName = true;

    cmd.ExecuteNonQuery();

    Score.ScoTipPodjetja = (long)(OracleDecimal)cmd.Parameters["o_sco"].Value;
}

2. Using Oracle Function

a. Oracle Function:

create or replace FUNCTION F_TIP_PODJETJA(tip in number) RETURN NUMBER
IS
    SCO   NUMBER;
BEGIN
    -- make sure your query always returns only one row
    -- otherwise you need to use a cursor or something else
    BEGIN
        EXECUTE IMMEDIATE 'select score 
            from sco_sif_score a
            where a.sif_kat = 3 and a.tip_pod = :a'
            INTO SCO USING tip; --tip = :a
        EXCEPTION
            WHEN NO_DATA_FOUND THEN NULL;
    END;
            RETURN SCO;
END F_TIP_PODJETJA;

b. C# Code:

using (OracleCommand cmd = new OracleCommand())
{
    cmd.Connection = conn;

    cmd.CommandText = "F_TIP_PODJETJA";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add(new OracleParameter("tip", Podjetje.TipSub));
    cmd.Parameters.Add(new OracleParameter("sco", OracleDbType.Decimal, ParameterDirection.ReturnValue));
    cmd.BindByName = true;

    cmd.ExecuteNonQuery();

    Score.ScoTipPodjetja = (long)(OracleDecimal)cmd.Parameters["sco"].Value;
}

Thanks again.

Kind regards, Flin

Answer 2

CREATE OR REPLACE FUNCTION P_TIP_PODJETJA(tip in number) RETURN NUMBER
IS
   SCO   NUMBER;
BEGIN
   -- make sure your query always returns only one row
   -- otherwise you need to use a cursor or something else
   BEGIN
      EXECUTE IMMEDIATE 'select score 
         from sco_sif_score a
         where a.sif_kat = 3 and a.tip_pod = :a'
         INTO SCO USING tip; --tip = :a
      EXCEPTION 
         WHEN NO_DATA_FOUND THEN NULL;
   END;
   RETURN SCO;
END P_TIP_PODJETJA;
/