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
页:
[1]