调用方法
/// <summary>
/// 用于牵涉到写入Orcale的string转clob录入数据库
/// </summary>
/// <param name="tempClobName">oracle临时变量名</param>
/// <param name="strContent">需要封装到oracle临时变量里的strContent</param>
/// <param name="sql">sql执行语句</param>
/// <returns></returns>
public static bool OrcaleSetClob(string tempClobName,string strContent, string sql)
{
bool flag = false;
//============================================
OracleConnection connt = new OracleConnection(ConfigurationSettings.AppSettings["AHPortalConnectionString"]);
connt.Open();
//要使用clob必须使用事务,要保证大文本完全被插入,否则失败
OracleTransaction oxt = connt.BeginTransaction();
OracleCommand cmd = connt.CreateCommand();
try
{
//事务负载在command的执行过程中.
cmd.Transaction = oxt;
//Step1.创建临时的Lob
//Oracle server syntax to obtain a temporary LOB.
cmd.CommandText = "DECLARE A clob; BEGIN DBMS_LOB.CREATETEMPORARY(A, FALSE); :LOC := A; END;";
//Bind the LOB as an output parameter.
OracleParameter p = cmd.Parameters.Add("LOC", OracleType.Clob);
p.Direction = ParameterDirection.Output;
//Execute (to receive the output temporary LOB).
cmd.ExecuteNonQuery();
//Return the temporary LOB.
OracleLob tempLob = (OracleLob)p.Value;
//Step 2.把文本转换为字节数组.
System.Text.UnicodeEncoding converter = new System.Text.UnicodeEncoding();
byte[] buffer = converter.GetBytes(strContent);
//Step 3.把字节数组封装到OracleLob对象里面去
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(buffer, 0, buffer.Length);
tempLob.EndBatch();
cmd.Parameters.Clear();
//Text方式定义
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add(new OracleParameter(tempClobName, OracleType.Clob)).Value = tempLob;
cmd.CommandText = sql;
//执行
flag = cmd.ExecuteNonQuery() > 0 ? true : false;
oxt.Commit();
connt.Close();
}
catch (Exception)
{
flag = false;
oxt.Commit();
connt.Close();
}
return flag;
}
前台