Oracle Data Access ORA-06512: character string buffer too low

Question

When I call GetTest, I get this error:

String Buffer Too Small ORA-06512

This is my C # method:

public string GetTEST()
{
    using (var conn = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["Dbconnection"].ConnectionString))
    {
        OracleCommand cmd = new OracleCommand("Package.GetTEST");
        cmd.BindByName = true;
        cmd.Connection = conn;
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.Add("P_OUT_MESSAGE", OracleDbType.Varchar2,1000,ParameterDirection.Output);

        cmd.ExecuteNonQuery();

        var t = cmd.Parameters["P_OUT_MESSAGE"].Value;
    }
}

Oracle procedure:

PROCEDURE GetTEST
(
  P_OUT_MESSAGE    OUT VARCHAR2 
)
IS
BEGIN
  p_out_message := 'Un problème a été signalé pour votre propriété. Veuillez communiquer avec le Service de l''évaluation au 418 111-7878 ou à l''adresse [e-mail]';
END;

Answer 1

This is clearly not what you want, but it seems that ODP.NET uses the length of the parameter on the .NET side as the length of the out parameter ...

This will fix your problem:

cmd.Parameters.Add("P_OUT_MESSAGE", OracleDbType.Varchar2, 32767, "x".PadRight(500, 'x'), ParameterDirection.Output);

But this is better, and although this is not entirely correct, it works:

cmd.Parameters.Add("P_OUT_MESSAGE", OracleDbType.Clob, ParameterDirection.Output);

Or, better yet, if possible, avoid using parameters outand use scalar return values ​​or table functions.