Stored procedure OUTPUT VARCHAR2 value truncated using 12c client

Question

I am using oracle 11g. My stored procedure is returning varchar2 but its value is being truncated by oracle client. Below is my code :

            if ((ds != null) && (ds.Tables.Count > 0))
                {

                    foreach (DataRow rw in ds.Tables[0].Rows)
                    {

                        OracleParameter param = new OracleParameter((rw["argument_name"]).ToString(), GetOracleType(rw["data_type"].ToString().ToUpper()));
                        param.Direction = GetParameterDirection((rw["in_out"]).ToString().ToUpper());
                        discoveryCommand.Parameters.Add(param);
                        if (param.Direction == ParameterDirection.Output && param.OracleType == OracleType.VarChar)
                        {
                            param.Size = 4000;
                        }
                    }
                }

I increased the param.size to 4000 but still values are being truncated. Is there any solution to this. On server I have Oracle 12c. I need to get solution without updating oracle client version in my project as that is not allowed due to some reasons.

Below is the SP . I modified it to return hard-coded values. Still same issue.

PROCEDURE access_level (
          p_emp_id IN  employees.emp_id%TYPE,
        p_id IN  NUMBER,
        p_type VARCHAR2,
          p_access_level OUT VARCHAR2
 ) IS
  BEGIN


 p_access_level := 'X' || 'RO' || 'RW';






 END IF;

Answer 1

I couldn't reproduce your problem for client version 11.2.0.1.0 connecting to server 12.1.0.1.0. It's a known case when Oracle 12c client truncates output variables, however if you're using client of 11g version, it shouldn't be your case.

I've used following test table and stored procedure:

CREATE TABLE TEST_TABLE
(
    ID NUMBER(11) NOT NULL,
    NAME VARCHAR2(256),
    CONSTRAINT TEST_TABLE_PK PRIMARY KEY (ID)
)
/

INSERT INTO TEST_TABLE(ID, NAME) VALUES(1, 'Some test data')
/

CREATE PROCEDURE TEST_PROCEDURE
(
   P_ID OUT NUMBER,
   P_NAME OUT VARCHAR2
)
AS
BEGIN
   SELECT ID, NAME INTO P_ID, P_NAME FROM TEST_TABLE;
END;

Here is the client code that correctly fetches data:

using (OracleConnection connection = new OracleConnection())
{
    connection.ConnectionString = ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString;
    connection.Open();

    using (OracleCommand command = connection.CreateCommand())
    {
        command.CommandText = "TEST_PROCEDURE";
        command.CommandType = CommandType.StoredProcedure;

        OracleParameter param1 = new OracleParameter("P_ID", OracleType.Number);
        param1.Direction = ParameterDirection.Output;
        command.Parameters.Add(param1);

        OracleParameter param2 = new OracleParameter("P_NAME", OracleType.VarChar);
        param2.Size = 4000;
        param2.Direction = ParameterDirection.Output;
        command.Parameters.Add(param2);

        using (command.ExecuteReader())
        {
            Console.WriteLine($"Output: [{param2.Value}]");
        }
    }
}

To proceed with your question could you please do the following:

1. If it's possible create above test table and stored procedure, and check how string data is fetched with the above code.

2. If for some reason it's not possible, please provide following info:

   • Full code of called stored procedure
   • DDL for all tables engaged in the stored procedure
   • Full client code that fetches data

The devil is always in the details. We should just understand what in your case differs from working exemplary code above.

Answer 2

I tried to resolve issue by migrating to ODP.NET as System.Data.OracleClient is being deprecated by Microsoft as mentioned here , but issue was not resolved. Below is how issue was resolved :

1. Oracle client version installed on machine 12.1.0.2.2
2. Output parameter truncation bug is mentioned in Oracle docs as Bug21616079
3. Oracle has given fix in version 12.2.0.1.0 as mentioned in Oracle documentation here.
4. So upgrading to version 12.2.0.1.0 from 12.1.0.2.2 fixed this issue for me as Oracle has given fix in this version only which is mentioned in official Oracle documentation for which I provided link above in point 3.