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 + "rn" + "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”文件里的数据库连接字符串。