(原) ODP.NET 演示以流的形式来获取 LOB 数据

using System;
using System.Data;
using System.Text;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.IO;

namespace Sample44
{
    // 演示以流的形式来获取 LOB 数据
    class Program
    {
        static void Main(string[] args)
        {
            // Connect
            string constr = "User Id=scott;Password=tiger;Data Source=bjoracle";
            OracleConnection conn = Connect(constr);

            // Setup
            Setup(conn);

            //Set the command
            OracleCommand cmd = new OracleCommand("select story from multimedia_tab where thekey=1");
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;

            // Execute
            OracleDataReader reader;
            try
            {
                // Create DataReader
                reader = cmd.ExecuteReader();

                //Read the first row
                while (reader.Read())
                {
                    // Set the OracleClob object to the CLOB selected
                    OracleClob clob = reader.GetOracleClob(0);

                    // Read data all data
                    System.Text.UnicodeEncoding converter = new System.Text.UnicodeEncoding();

                    //byte[] clob_data = new byte[1024];
                    Int64 amountRead = 0;
                    int readSize = 8;
                    Int64 totalRead = 0;

                    //do
                    //{
                    //    amountRead = clob.Read(clob_data, (int)totalRead, readSize);
                    //    Console.WriteLine("Actual read: {0} bytes", amountRead);
                    //    Console.WriteLine("Current: {0}", converter.GetString(clob_data));
                    //    totalRead += amountRead;
                    //} while (amountRead > 0);

                    string str = string.Empty;
                    do
                    {
                        byte[] clob_data = new byte[8];
                        amountRead = clob.Read(clob_data, 0, readSize);
                        str+=converter.GetString(clob_data);
                        totalRead += amountRead;
                    } while (amountRead > 0);

                    Console.WriteLine("Total number of bytes read: {0}", totalRead);
                    Console.WriteLine("Story: {0}", str);
                    // Dispose OracleClob object
                    clob.Dispose();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }
            finally
            {
                // Dispose OracleCommand object
                cmd.Dispose();

                // Close and Dispose OracleConnection object
                conn.Close();
                conn.Dispose();
            }

            // Waiting
            Console.ReadLine();
        }

        /**//// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="connectStr"></param>
        /// <returns></returns>
        public static OracleConnection Connect(string connectStr)
        {
            OracleConnection conn = new OracleConnection(connectStr);
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }
            return conn;
        }

        /**//// <summary>
        /// 创建必要的表和测试数据
        /// </summary>
        /// <param name="conn"></param>
        public static void Setup(OracleConnection conn)
        {
            StringBuilder blr;
            OracleCommand cmd = new OracleCommand("", conn);

            blr = new StringBuilder();
            blr.Append("DROP TABLE multimedia_tab");
            cmd.CommandText = blr.ToString();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }

            blr = new StringBuilder();
            blr.Append("CREATE TABLE multimedia_tab(thekey NUMBER(4) PRIMARY KEY,");
            blr.Append("story CLOB, sound BLOB)");
            cmd.CommandText = blr.ToString();
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }

            // 插入大对象
            string str = string.Empty;
            using (StreamReader tr = new StreamReader(@"C:\arcgis9.2\tt.txt"))
            {
                str=tr.ReadToEnd();
            }

            blr = new StringBuilder();
            blr.Append("INSERT INTO multimedia_tab values(");
            blr.Append("1,");
            blr.Append(":story,");
            blr.Append("'123456789545454545454545454554545454545454')");
            cmd.CommandText = blr.ToString();
            //cmd.CommandText = "EXECMULT";
            //cmd.CommandType = CommandType.StoredProcedure;

            OracleParameter param = new OracleParameter("story", OracleDbType.Clob);
            param.Direction = ParameterDirection.Input;
            param.Value = str;
            cmd.Parameters.Add(param);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: {0}", ex.Message);
            }
            finally
            {
                cmd.Dispose();
                str = null;

            }
        }
    }
}

注意问题：
1、从数据库中取大数据时，要一部分一部分的取，然后存入存储设备中，这样可以节省内存的消耗。
2、在插入 clob 类型的数据时，要看数据量的大小，当小于2000字符时，可以直接插入，否则参与参数的形式插入。
   多于2000字符出现 ORA-01704:文字字符串过长 的错误，解决方案见：http://www.cnblogs.com/mjgforever/archive/2007/12/27/1016515.html