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);//反序列化取出字符数据

                    }}