Oracle中Clob與Blob欄位的讀取

Oracle中若以字串的形式寫入資料到Clob欄位中，預設的長度為4000，最大可儲存4G。

     直接將字串寫入Clob欄位中，則最大可寫4000個位元組；若要寫入大的字元資料，可以將字元資料以二進位流的形式寫入。

寫入Clob(Blob代碼):

OracleConnection conn = new OracleConnection(GetConfigUserConnectionString());
            conn.Open();
            OracleTransaction tran = conn.BeginTransaction();
            string id = string.Empty;
            OracleCommand command = null;
            try
            {
                command = new OracleCommand();
                command.Connection = conn;
                double scale = 0;
                if (ct.Scale.IndexOf(":") == -1)
                {
                    scale = BasicOperate.GetDouble(ct.Scale, true);
                }
                else
                {
                    scale = BasicOperate.GetDouble(ct.Scale.Substring(ct.Scale.IndexOf(":") + 1), true);
                }

//在插入時先存一個空的empty_clob()[empty_blob()]
                command.CommandText = string.Format(@"insert into chart(chartname,charttype,source,scale,producttime,"
                    +"xml_definition) values('{0}','{1}','{2}',{3},'{4}',empty_clob()) "
                    +"returning chartid into :rid",
                     ct.ChartName, ct.ChartType, ct.Source, scale, ct.ProductTime, ct.xmlDefinition);
                command.Parameters.Add("rid", OracleDbType.Varchar2, 32).Direction = ParameterDirection.Output;
                command.ExecuteNonQuery();
                id = command.Parameters["rid"].Value.ToString();

//讀入clob欄位進行編輯
                command.CommandText = string.Format("select xml_definition from chart where chartid='{0}' for update", id);
                using (OracleDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        OracleClob clob = reader.GetOracleClob(0);//讀入二進位對性
                        clob.Erase();//清空其中的資料
                        clob.Position = 0;
                        clob.BeginChunkWrite();//開始寫入
                        int buffersize = 100;
                        int retval = 0;
                        byte[] bts = new byte[buffersize];

//將字串序列化為二進位流
                        MemoryStream stream = new MemoryStream();
                        BinaryFormatter formatter = new BinaryFormatter();
                        formatter.Serialize(stream, ct.xmlDefinition);

//將二進位流寫入Clob字元中
                        stream.Seek(0, SeekOrigin.Begin);
                        retval = stream.Read(bts, 0, buffersize);
                        while (retval == buffersize)
                        {
                            clob.Write(bts, 0, buffersize);
                            retval = stream.Read(bts, 0, buffersize);
                        }
                            clob.Write(bts, 0, 100);
                        clob.EndChunkWrite();//結束寫入
                        clob.Flush();//重新整理
                        clob.Close();//關閉 
                    }
                    reader.Close();
                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                //throw new Exception(ex.Message);
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

 

 

讀取Clob(blob)欄位:

 

。。。 

 using (OracleDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        OracleClob clob = reader.GetOracleClob(0);//讀取二進位欄位
                        clob.Position = 0;//指向起點
                        byte[] tt = new byte[clob.Length];
                        clob.Read(tt, 0, (int)clob.Length);//將Clob讀為位元據
                        MemoryStream ms = new MemoryStream(tt);
                        BinaryFormatter bb = new BinaryFormatter();
                        object oo = bb.Deserialize(ms);//還原序列化取出字元資料

                    }}