Microsoft-IIS/8.5 , Oracle output variables are truncated

Question

OracleConnection connection = DBHelper.OracleConnection;
OracleCommand OraCommand = connection.CreateCommand();

OraCommand.CommandText = "AUTHENTICATION.Authenticate";
OraCommand.CommandType = CommandType.StoredProcedure;

int zero = 0;
OracleParameter newParam = null;


OraCommand.Parameters.Add(newParam);
newParam = new OracleParameter("Authenticated", OracleType.VarChar);
newParam.Direction = ParameterDirection.Output;
newParam.Size = 4000;


OraCommand.Parameters.Add(newParam);
newParam = new OracleParameter("Message", OracleType.VarChar);
newParam.Direction = ParameterDirection.Output;
newParam.Size = 4000;
OraCommand.Parameters.Add(newParam);


newParam = new OracleParameter("Response", OracleType.VarChar);
newParam.Direction = ParameterDirection.Output;
newParam.Size = 4000;
OraCommand.Parameters.Add(newParam);

try
{
        connection.Open();
        OraCommand.ExecuteNonQuery();
        connection.Close();

        errorLabel.Text = OraCommand.Parameters["Message"].Value.ToString() ;

        if (OraCommand.Parameters["Authenticated"].Value.ToString() == "Yes")
        {


            this.Response.Redirect("Default.aspx", true);
        }else
        {
            errorLabel.Text = OraCommand.Parameters["Message"].Value.ToString() + Request.ServerVariables["SERVER_SOFTWARE"] + OraCommand.Parameters[9].Value.ToString();
        }

    }
    catch (Exception ex)
    {
        errorLabel.Text = ex.ToString();
    }

Expected outputs:

1) Message - You have successfully logged in

but I'm getting a truncated string: You have success

2) Authenticated - Yes

but I'm getting a truncated string

Y

Same code worked fine in IIS 7.5, we upgraded our server to IIS 8.5 now I'm facing this problem .

I did read some articles about ado.net deprecated and use odp.net . I dont want to change my code to ODP.net .

Do you have any thoughts , why my output variables are truncated ??

When we upgraded IIS 8.5 we installed 12.1.0 instant client on that machine. Does this cause the problem ???

Answer 1

This is the bug in Oracle Client 12c. It truncates not only strings but numbers as well. Reverting to client 11g fixed the problem.

Answer 2

newParam = new    OracleParameter("Response",OracleType.VarChar);
Use **OracleType.Char** instead of     **OracleType.VarChar** 
newParam = new    OracleParameter("Response", OracleType.Char);
 IT works in my case.

I am using Oracle 11g and VS12 ,truncating output parameter in dot net code, I even changed the output parameter as integer in Stored procedure.

When I changed to Char instead of Varchar as below in dot net it works fine.

db.AddParameter(dbCommand, "p_out_result", OracleType.Char, 300, 
ParameterDirection.Output, true, 100, 3, null, DataRowVersion.Default, null);