Get Return of Procedure on Oracle

Question

I need to run a database (Oracle) that returns me a character (S or N), I’m using the ODP.NET driver, I tried everything and could not do it, including I looked at several answers right here in S.O PT and S.O EN also.

I put the code below to inform that is command type is a precedent.

cmd.CommandType = System.Data.CommandType.StoredProcedure;

I informed the output parameter:

OracleParameter param = cmd.Parameters.Add("p_retorno", OracleDbType.Varchar2);
param.Direction = System.Data.ParameterDirection.Output;

I tried to take the return like this:

string retorno = cmd.Parameters["p_retorno"].Value.ToString();

thus:

string retorno = (string)cmd.ExecuteScalar();

and so:

OracleDataReader data = cmd.ExecuteReader();
string retorno = data.GetString(0);

But the return variable always comes empty, proc is executed normally but I can’t catch the return.

Answer 1

Missing set size of VARCHAR

 static void Main(string[] args)
    {
        var connection = new OracleConnection();
        try
        {
            using (var oracleCommand = new OracleCommand("PROC_TESTE", connection)
            {
                CommandType = CommandType.StoredProcedure
            })
            {

                var pRetorno = new OracleParameter("P_RET", OracleDbType.Varchar2, ParameterDirection.Output);
                pRetorno.Size = 1;
                oracleCommand.Parameters.Add(pRetorno);

                connection.Open();

                var b = oracleCommand.ExecuteNonQuery();
                Console.WriteLine(pRetorno.Value);
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.ToString());
        }
        finally
        {
            if (connection.State == ConnectionState.Open)
                connection.Close();
        }
}

A test proc:

create or replace PROCEDURE PROC_TESTE
(
    P_RET OUT VARCHAR2
) IS 
BEGIN 
  P_RET := '2';
END;