使用ODP .NET使用C#从Oracle数据库读取BLOB [英] Reading a BLOB from Oracle Database with C# using ODP .NET

问题描述

我在从Oracle DB获取Blob时遇到问题，我在做一个简单的C#控制台项目，尝试从Google进行示例练习，试图找出问题所在.我正在做一个选择语句以获取数据.该程序可以工作，但是如果我包含从表中获取的Blob类型列("XML_AND_PDF_ZIP")，则会收到错误ORA-03135:执行OracleDataReader Object的Read()方法时出现连接丢失提示.

I'm having problems getting a Blob from Oracle DB, I'm doing a simple C# console Project trying sample exercises from google trying to figure out whats the problem. I'm making a Select Statement to getting the data. This program works but if I include the blob type column("XML_AND_PDF_ZIP") which is the column I want from the table I get the error ORA-03135: Connection Lost Contact Tips when the Read() method of the OracleDataReader Object executes.

我正在使用Oracle.ManagedDataAccess作为我的ODP .NET提供程序.Oracle数据库版本为10g 10.2 64位.
在ExecuteReader()方法命中之前，我是否缺少某些东西?

I'm using Oracle.ManagedDataAccess as my ODP .NET provider. The Oracle Database version is 10g 10.2 64bit.
Am I missing something before the ExecuteReader() method hits ??

private static string GetConnectionString(string _connectionID)
        {
            return ConfigurationManager.ConnectionStrings[_connectionID].ConnectionString;
        }

    public static List<CF2> GetOraBlob(string sFolio)
    {
        try
        {
            List<CF2> result = new List<CF2>();
            using (OracleConnection conn = new OracleConnection(GetConnectionString(connectionID)))
            {
                using (OracleCommand cmd = conn.CreateCommand())
                {

                    cmd.CommandText = "SELECT COMPANY_EMPID_FOLIO, XML_AND_PDF_ZIP  FROM CF2 WHERE COMPANY_EMPID_FOLIO = :pCOMPANY_EMPID_FOLIO FOR UPDATE"; //XML_AND_PDF_ZIP is the Blob Column
                    cmd.Parameters.Add("pCOMPANY_EMPID_FOLIO", OracleDbType.Varchar2, 15).Value = sFolio;
                    cmd.CommandType = CommandType.Text;
                    cmd.BindByName = true;
                    conn.Open();
                    using (OracleDataReader reader = cmd.ExecuteReader())
                    {
                            while (reader.Read()) //Get ORA-03135 Error If the Blob Column is in the Select Statement
                            {

                                CF2 data = new CF2();
                                data.Folio = reader["COMPANY_EMPID_FOLIO"].ToString();
                                data.OraBlob = Convert.ToByte(reader["XML_AND_PDF_ZIP"]);
                                result.Add(data);

                            }

                    }
                }
            }
            return result;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

推荐答案

前段时间有类似的问题，我使用以下解决方案:

Some time ago a had similar problem and I use this solution:

创建PL/SQL函数:

Create a PL/SQL Function:

FUNCTION GetBlob(pCOMPANY_EMPID_FOLIO IN NUMBER) RETURN SYS_REFCURSOR IS
    res SYS_REFCURSOR;
BEGIN
    OPEN res FOR 
    SELECT XML_AND_PDF_ZIP  
    FROM CF2 
    WHERE COMPANY_EMPID_FOLIO = pCOMPANY_EMPID_FOLIO;
    RETURN res; 
END GetBlob; 

并这样称呼它:

OracleCommand cmd = new OracleCommand("BEGIN res := GetBlob(:primaryKey); END;"), ora.Connection);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("res", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
cmd.Parameters.Add("primaryKey", OracleDbType.Int32, ParameterDirection.Input).Value = sFolio;
cmd.InitialLOBFetchSize = 16384;
using (OracleDataReader dr = cmd.ExecuteReader() ) {
   dr.Read();
   OracleBlob blob = dr.GetOracleBlob(0);
   MemoryStream ms = new MemoryStream(blob.Value);
   dr.Close();
}

注意，我的应用程序使用ODP.NET非托管驱动程序.也许您的问题是由于托管驱动程序中的错误所致.

Note, my application uses the ODP.NET Unmanaged Driver. Perhaps your issue is due to a bug in the Managed Driver.

这篇关于使用ODP .NET使用C#从Oracle数据库读取BLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！