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 ???

Yes, we are also running into same issue only on the machines which has only 12.1.0 or > oracle client. In their documentation, they say it is 100% compatible from 9 to 12c server. But it is not. I did do a Unit test converted to ODP.NET ( instead of using the depricated System.Data.Oracle, from msft) , the truncation issue is gone. — Sanjay10, Mar 02 '15 at 20:44
Anyone else having issues with truncation of data ( using System.Data.Oracle , with 12.1.0 client installation) ? — Sanjay10, Mar 02 '15 at 20:44
I am having the same issue. switching the oracle type to fixed length returns the values. but then it has a bunch of extra spaces. — Steve Coleman, Apr 02 '15 at 22:41

score 13 · Answer 1 · answered May 01 '15 at 19:43

13

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

answered May 01 '15 at 19:43

Vick Rom

149
1
4

Confirming the issue with IIS7.5 and Windows 2008 R2. The ODAC 12c Release 3 (12.1.0.2.1) Xcopy for Windows released December 23, 2014 was causing truncation on my Stored Procedures out parameters. Reverting back to ODAC 11.2 Release 6 (11.2.0.4.0) released on January 14, 2014 solved the truncation issue. Download link: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html – mitaka Jun 25 '15 at 13:48
ODAC 12.1.0.2.4 still performs truncation as outlined above. Reverting to 11.2.0.4.0 fixed the issue. **Workaround** To support both clients on the box, update your path in a .bat file before launching your app. For example, to use Oracle 11 client `path=C:\Oracle\Oracle11_Client;%path%` – bdeem Mar 03 '16 at 15:21

score 2 · Answer 2 · edited Apr 23 '19 at 06:01

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

score 0 · Answer 3 · answered May 04 '15 at 22:16

We had the same experiance as you did when migrating to Oracle 12c database (on Oracle servers) and to a Windows 2012 for webservices (IIS 8).

Varchar2 string, returned from a stored procedure, was truncated almost always at half - no mather what setting we used.

Replacing the Oracle client 12 64-bit with client 12 32-bit did NOT solve the problem.

The solution of Vick Rom solved our problem. Oracle client 11 was installed on a 64bit Windows 2012 server.

We are planning to keep it that way until Oracle client 12 won't be fixed.

score 0 · Answer 4 · answered Sep 10 '15 at 17:19

I have the same problem, But I found a work around while you still using oracle 12 and 11 installed on the same machine. below is what I did.

1- Oracle 11 was already installed in my case so I keep it there.

2- I installed oracle client 12c. copied the TNS names after the installation.

3- Installed oracle 11 (win32_11gR2_client). copied the TNS names files.

4- restarted the server.

Everything worked very well. the system using oracle client managed driver worked and the old system using system.data.oracleclient worked as well. Thank you

where the tnsnames.ora file would go in the client? I'm used to put it in the Network\admin folder but in the client version there's no such folder tree....thx! — Black.Jack, Nov 28 '17 at 21:25

score 0 · Answer 5 · answered May 16 '16 at 19:32

I had the same issue, but chose a different approach due to my client's policy to stick with latest Oracle Client. In this case, I have to make my code work with Oracle 12c client on Windows 2012 R2 [IIS 8.5] server.

Instead of returning a varchar2 as output paramter, I modified the SP to return a SYS_REFCURSOR with one row/column in it.

Ex:

PROCEDURE get_access_sp (p_mode IN VARCHAR2, p_out OUT SYS_REFCURSOR)
IS
BEGIN
   /*Body of SP*/
   v_out := 'TEST_SP_RETURN_PARAM';

   --RETURN
   OPEN p_out FOR SELECT v_out po FROM DUAL;
EXCEPTION
   WHEN OTHERS
   THEN
      OPEN p_out FOR SELECT 'N' po FROM DUAL;
END get_access_sp;

Microsoft-IIS/8.5 , Oracle output variables are truncated

5 Answers5

Linked