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;

what library do you use. can you switch to Oracle.ManagedDataAccess? first it's native oracle and second it will allow you to not bother matching versions of the app-ora client-database — Jury Golubev, Mar 17 '17 at 16:34
Could you please provide actual data: what string is returned from stored procedure and how it's truncated? Also, does your procedure actually returns varchar2, not nvarchar2? — CodeFuller, Mar 20 '17 at 09:06
Actual string returned from procedure is "X|RO|RW". And it is truncated to "X|R". Parameter in stored procedure is VARCHAR2 @CodeFuller — Ubaid Ashraf, Mar 20 '17 at 09:08
@CodeFuller Also I am logging values returned from Stored procedure. It is returning correct values — Ubaid Ashraf, Mar 20 '17 at 09:09
Is it true that `param.Size = 4000` is being hit? That is, that parameter direction not say InputOuput, type is not NVarChar etc — Lanorkin, Mar 20 '17 at 10:35
I ended up fixing this by switching the parameter to CLOB type. You can just interpret the result as byte stream, and convert it to text using `Encoding.Unicode.GetString`. — Nyerguds, Dec 02 '21 at 12:39

score 5 · Answer 1 · edited May 23 '17 at 11:33

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:

If it's possible create above test table and stored procedure, and check how string data is fetched with the above code.
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.

If there is a known case it would be nice if it pointed to an Oracle bug. — stili, Jun 15 '18 at 07:41

score 4 · Accepted Answer · answered Mar 28 '17 at 16:45

4

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 :

Oracle client version installed on machine 12.1.0.2.2
Output parameter truncation bug is mentioned in Oracle docs as Bug21616079
Oracle has given fix in version 12.2.0.1.0 as mentioned in Oracle documentation here.
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.

answered Mar 28 '17 at 16:45

Ubaid Ashraf

1,049
5
15
43

1

I am having the same exact issue . I have 12.2.0.1.0 and still the output varchar is truncated to "TR" for true – xrx215 Mar 06 '18 at 14:15
I still have the same problem although update from Oracle 12.1 to 12.2. – Vazedias Aug 08 '19 at 15:22

score 0 · Answer 3 · answered Sep 30 '20 at 13:35

0

I experienced same issue with client 10g and server 11g both on 64-bit windows OS, I circumvented it by hosting my .net application on IIS and altered app_pool's advanced settings "Enable 32-Bit Applications" to False.

answered Sep 30 '20 at 13:35

Naveed I

21
3

Stored procedure OUTPUT VARCHAR2 value truncated using 12c client

3 Answers3

Linked