使用12c客户端截断存储过程OUTPUT VARCHAR2值 [英] Stored procedure OUTPUT VARCHAR2 value truncated using 12c client

问题描述

我正在使用 oracle 11g .我的存储过程返回了varchar2，但是其值被oracle client截断了. 下面是我的代码:

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

我将param.size增加到了4000，但仍然值被截断了. 有没有解决的办法.在服务器上，我有 Oracle 12c .我需要在不更新我的项目中oracle客户端版本的情况下获得解决方案，因为某些原因，这是不允许的.

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.

下面是SP.我对其进行了修改，以返回硬编码的值.还是同样的问题.

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;

推荐答案

我尝试通过迁移到ODP.NET来解决问题，因为System.Data.OracleClient已被Microsoft弃用，如

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. 安装在计算机12.1.0.2.2
上的Oracle客户端版本 在Oracle文档中将
2. Output parameter truncation错误提到为Bug21616079
3. Oracle已提供了Oracle文档

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.

这篇关于使用12c客户端截断存储过程OUTPUT VARCHAR2值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！