C#处理oracle数据库的Clob类型

通过C#语言，使用.net的ado组件录入oracle数据库的Clob类型字段：

1。当录入字符串过长，导致ado的命令Command的CommandText字符串大于4056，录入会失败，提示：

ORA-01704字符串文字太长。

2。同样情况下，如果采用通过ado的命令Command访问oracle存储过程，并且使用命令参数形式传入超长的字符串，当字符串本身长度大于32512时，录入才会失败，提示：

ORA-01460 转换请求无法实现或不合理。

出现上述两种情况，可以用下面代码解决：

 

 /// <summary>
        /// 处理clob数据后保存，成功
        /// </summary>
        private void button1_Click(object sender, EventArgs e)
        {
            string ClobString = CreateClobString();           

            string ConnectionString = ConfigurationManager.AppSettings["ConnectionString"].ToString();
            System.Data.OracleClient.OracleConnection oc = new System.Data.OracleClient.OracleConnection(ConnectionString);
            oc.Open();
            try
            {
            OracleCommand command = new OracleCommand();
            command.Connection = oc;
            command.CommandType = CommandType.Text;
            command.CommandText = "INSERT INTO demo_table (demo_id,demo_clob) VALUES ("+id.ToString()+", 'aa')";
            richTextBox1.Text = "SQL语句字符串长度：" + command.CommandText.Length + "\r\n" + "SQL语句字符串内容：";
            richTextBox1.Text += command.CommandText;
            int ii = command.ExecuteNonQuery();
            //修改Clob数据
            if (ii > 0)
                UpdateClobData(oc, "SELECT demo_clob FROM demo_table WHERE demo_id=" + id + " FOR UPDATE", ClobString);
            }
            catch (Exception ex)
            {
                MessageBox.Show("保存失败：" + ex.Message);
            }
            finally
            {
                oc.Close();
            }
        }

#region Oracle大对象处理

        /// <summary>
        /// // CreateTempLob
        /// </summary>        
        private OracleLob CreateTempLob(OracleCommand cmd, OracleType lobtype)
        {
            if (lobtype == null) return null;
            if (cmd == null) return null;

            //Oracle server syntax to obtain a temporary LOB.
            cmd.CommandText = "DECLARE A " + lobtype + "; " +
                           "BEGIN " +
                              "DBMS_LOB.CREATETEMPORARY(A, FALSE); " +
                              ":LOC := A; " +
                           "END;";

            //Bind the LOB as an output parameter.
            OracleParameter p = cmd.Parameters.Add("LOC", lobtype);
            p.Direction = ParameterDirection.Output;

            //Execute (to receive the output temporary LOB).
            cmd.ExecuteNonQuery();

            //Return the temporary LOB.
            return (OracleLob)p.Value;
        }

        /// <summary>
        /// 修改Clob数据
        /// </summary>
        public void UpdateClobData(OracleConnection sc, string SQLString, string ReportString)
        {
            if (sc == null) return;
            if (string.IsNullOrEmpty(SQLString)) return;
            if (string.IsNullOrEmpty(ReportString)) return;

            //用OracleLob输入Clob数据
            OracleCommand cmd = sc.CreateCommand();
            cmd.Transaction = cmd.Connection.BeginTransaction();
            cmd.CommandText = SQLString;
            OracleDataReader reader = cmd.ExecuteReader();
            using (reader)
            {
                reader.Read();
                OracleLob blob = reader.GetOracleLob(0);
                OracleLob templob = CreateTempLob(cmd, blob.LobType);
                byte[] buffer = System.Text.Encoding.Unicode.GetBytes(ReportString);
                //将文件内容传入Blob变量
                templob.BeginBatch(OracleLobOpenMode.ReadWrite);
                templob.Write(buffer, 0, buffer.Length);
                templob.Position = 0;
                long actual = templob.CopyTo(blob);
                cmd.Transaction.Commit();
            }
        }
        
        #endregion           

下面是示例代码地址：

 http://files.cnblogs.com/wrzhao/DemoApplication.rar

下载后，需要执行“demoSQL.sql”建立程序运行所需表和存储过程；需要修改“App.config”文件里的数据库连接字符串。

转载于:https://www.cnblogs.com/wrzhao/archive/2012/09/12/wrzhao.html