Get procedure return in Oracle

Question

Get procedure return in Oracle

Navigation

#1 by (0 votes)

1

I need to execute a procedure in the database (Oracle) that returns me a character (S or N), I'm using the ODP.NET driver, I've tried everything and I could not do this, I even looked at several answers right here in SO PT and OS EN as well.

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

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

I gave the output parameter:

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

I tried to get the return like this:

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

like this:

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

and so:

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

But the return variable is always empty, the proc is executed normally but I can not get the return.

c# oracle asp.net-mvc

asked by anonymous 07.12.2016 / 17:56

1 answer

Problem in displaying the wpf datagrid control How to pass more than one parameter in a paint () method in Java?

score 0 · Answer 1

Missing set VARCHAR size

 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();
        }
}

The test proc:

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