C#读取Oracle的Long字段中的长字符串

有的人在Oracle数据库中用Long类型存储长字符串(例如转成Base64的图片), 官方已经不推荐了, 但项目中还是遇到了, 读取方法如下
C#读取Oracle的Long字段中的长字符串(大于32K)
关键代码是: command.InitialLONGFetchSize = -1;

var ConnString = lblConnString.Text
using (OracleConnection connection = new OracleConnection(ConnString))
{
        var sql = "select xxxxx "
        connection.Open();
        OracleCommand command = new OracleCommand(sql, connection);
        command.InitialLONGFetchSize = -1;
        OracleDataReader rdr = command.ExecuteReader();
        DataTable dt = new DataTable();
        dt.Load(rdr);
        connection.Close();
        var imgdata = dt.rows[0][1].ToString()
}

如果Long类型字段中存储的字符串长度小于32K可以先在数据建一个函数把long字段转换为varchar2(32767),然后就可以直接查询了

CREATE OR REPLACE FUNCTION LONG_TO_CHAR(
       in_rowid rowid,
       in_table_name varchar,
       in_column varchar2)
RETURN varchar AS
       text_c1 varchar2(32767);
       sql_cur varchar2(2000);
begin
      DBMS_OUTPUT.ENABLE (buffer_size=>null);
      sql_cur := 'select '||in_column||' from '||in_table_name||' where rowid = '||chr(39)||in_rowid||chr(39);
      dbms_output.put_line (sql_cur);
      execute immediate sql_cur into text_c1;
      text_c1 := substr(text_c1, 1, 4000);
      RETURN TEXT_C1;
END;

查询脚本示例:

select id,name,LONG_TO_CHAR(ROWID,'tablename','columnname') from table