Enterprise library处理Oracle Lob

最新推荐文章于 2025-08-08 17:29:46 发布

zhang_kevin_wei

最新推荐文章于 2025-08-08 17:29:46 发布

阅读量660

点赞数

分类专栏： Oracle C# 文章标签： oracle library string stream exception integer

C# 同时被 2 个专栏收录

10 篇文章

订阅专栏

Oracle

8 篇文章

订阅专栏

本文介绍了在 Oracle 数据库中如何处理超过 32767 字节的大文本（CLOB）数据插入问题，包括使用存储过程进行一般处理的方法及针对大数据量的特殊处理技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE TABLE CLOB_TEST
(
ID           INTEGER                          NOT NULL,
INPUTTEXT    NCLOB,
CREATE_DATE DATE                      DEFAULT)

//一般的处理

public void InsertClob(string value)

{

OracleDatabase dbOracle = (OracleDatabase)DatabaseFactory.CreateDatabase();
            string sqlcomm = "pk_product_temp.sp_insert_productupapply";
            OracleCommand oracleComm = (OracleCommand)dbOracle.GetStoredProcCommand(sqlcomm);
            dbOracle.AddInParameter(oracleComm, "p_product_code", DbType.String, _Entity.PRODUCT_CODE);
            dbOracle.AddParameter(oracleComm, "p_clob_text", OracleType.Clob, value.Length,
                ParameterDirection.Input, true, 0, 0, "INPUTTEXT", DataRowVersion.Default, value);
            dbOracle.ExecuteNonQuery(oracleComm);

}

//大于32767，oracle缓冲只有32767字节

public void InsertClob(string value)

{

            OracleDatabase db = (OracleDatabase)DatabaseFactory.CreateDatabase();
            OracleConnection connect = (OracleConnection)db.CreateConnection();
            connect.Open();

            //一定要用事物
            OracleTransaction tran = connect.BeginTransaction();
            try
            {
                string sqlCommand = "pk_product_temp.insert_clob";
                OracleCommand dbcomm = connect.CreateCommand();
                dbcomm.Transaction = tran;
                dbcomm.CommandText = sqlCommand;
                dbcomm.CommandType = CommandType.StoredProcedure;
                dbcomm.Parameters.Add(new OracleParameter("p_clob_text", "test"));
                dbcomm.Parameters.Add(new OracleParameter("p_id", OracleType.Int32)).Direction = ParameterDirection.Output;

dbcomm.ExecuteNonQuery();
string id = dbcomm.Parameters["p_id"].Value.ToString();

                dbcomm.Parameters.Clear();
                dbcomm.CommandText = "select inputtext from clob_test where id = " + id + " for update";
                dbcomm.CommandType = CommandType.Text;

                using (OracleDataReader reader = dbcomm.ExecuteReader())
                {
                    while (reader.Read())
                    {

                        //保证要能取得数据，否则clob.Connection为空，无法Write()数据
                        OracleLob clob = reader.GetOracleLob(0);//读入二进制对性
                        clob.Erase();//清空其中的数据
                        clob.Position = 0;
                        clob.BeginBatch(OracleLobOpenMode.ReadWrite);//开始写入
                        int buffersize = 1000;
                        int retval = 0;
                        byte[] bts = new byte[buffersize];

                        //将字符串序列化为二进制流
                        MemoryStream stream = new MemoryStream();
                        BinaryFormatter formatter = new BinaryFormatter();
                        formatter.Serialize(stream, value);

                        //将二进制流写入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, 1000);
                        clob.EndBatch();//结束写入
                        clob.Flush();//刷新
                        clob.Close();//关闭

                    }
                }
                tran.Commit();
            }
            catch (Exception ex)
            {
                tran.Rollback();
                string err = ex.Message;
            }

}

引用http://blog.youkuaiyun.com/huangbomeizi/archive/2009/06/20/4284967.aspx