我们最近将解决方案升级到了新的Managed ODP.Net（从v4.121.1.0到v4.121.2.0），并且当字段的字符数在1001到4000个字符之间时，遇到了对CLOB字段进行换行的问题。当您尝试执行此操作时，将从ODP.Net抛出错误ORA-1461。
使用ODP.Net的早期版本运行相同的代码和数据，即可正常工作。此外，您可以插入1001和4000个字符的记录，只是无法更新它们。
我已经用C＃创建了一个示例程序来演示该问题。该程序执行以下操作：
它在数据库中创建一个3列的表，其中一个列为CLOB。
它创建一个内存中的.Net DataSet对象进行匹配。
在数据集中使用CLOB字段中的1400个字符创建新记录。
使用INSERT语句将DataSet保存到数据库。
用一些新数据更新DataSet中的CLOB字段，同样是价值1400个字符的数据。
将数据集保存到数据库，并引发ORA-1461。
我没有Oracle支持帐户，因此您在哪里向ODP.Net报告Oracle问题？
演示问题的示例C＃代码：
using System;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using Oracle.ManagedDataAccess.Client;
class CLOBTest
{
string _TableName = "CLOBTEST";
string _ServerName = "servername";
string _UserName = "username";
string _Password = "password";
public void CLOBTest1()
{
// Create a physical data table, if needed, in the Oracle DB that has the CLOB column
CreateTable();
// Create a dataset for the CLOBTEST table, fill it with a new row
DataSet CLOBInfo = BuildCLOBTestDataSet();
DataTable CLOBTable = CLOBInfo.Tables[_TableName];
DataRow CLOBRow = CLOBTable.NewRow();
CLOBRow["ACTION_CODE"] = DateTime.Now.ToString("s");
CLOBRow["DESCRIPTION"] = "CLOB Slim Test";
// The size of text in the CLOB field is critical to reproducing this defect.
// It *only* happens when the field has between 1001 and 4000 characters.
int LOBFieldSize = 1400;
string CLOBText = DateTime.Now.ToString("s") + " " + new string('-', LOBFieldSize);
CLOBRow["SCRIPT_TEXT"] = CLOBText.Substring(0, LOBFieldSize);
CLOBTable.Rows.Add(CLOBRow);
// Add that row to the DB, and then mark the DS with AcceptChanges
InsertRow(CLOBInfo);
// Update that row with some new data.
CLOBText = DateTime.Now.ToString("s") + " :: " + CLOBText;
CLOBRow["SCRIPT_TEXT"] = CLOBText.Substring(0, LOBFieldSize);
// Error (ORA-1461) happens in the UPDATE when the CLOB has 1001 - 4000 characters in it.
UpdateRow(CLOBInfo);
}
private void CreateTable()
{
if (TableExists())
return;
using (OracleConnection oc = OpenConnection())
{
using (OracleCommand ocmd = oc.CreateCommand())
{
string SQL = "CREATE TABLE " + _TableName + " (ACTION_CODE VARCHAR2(30) NOT NULL, DESCRIPTION VARCHAR2(50) NOT NULL, SCRIPT_TEXT CLOB, CONSTRAINT CLOBTEST_PK PRIMARY KEY (ACTION_CODE))";
ocmd.CommandText = SQL;
ocmd.ExecuteNonQuery();
AddLogMessage("Table created.");
}
}
}
private bool TableExists()
{
using (OracleConnection oc = OpenConnection())
{
using (OracleCommand ocmd = oc.CreateCommand())
{
string SQL = "SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = '" + _TableName + "'";
ocmd.CommandText = SQL;
object teRaw = ocmd.ExecuteScalar();
bool te = (bool)(int.Parse(teRaw.ToString()) > 0);
AddLogMessage("Table exists? " + te.ToString());
return te;
}
}
}
private void InsertRow(DataSet CLOBInfo)
{
string SQL = "INSERT INTO " + _TableName + " (ACTION_CODE, DESCRIPTION, SCRIPT_TEXT) VALUES (:pACTION_CODE, :pDESCRIPTION, :pSCRIPT_TEXT)";
using (OracleConnection oc = OpenConnection())
{
using (OracleDataAdapter oda = new OracleDataAdapter())
{
using (OracleCommand ocmd = oc.CreateCommand())
{
CreateDataParameters(ocmd);
ocmd.CommandText = SQL;
oda.InsertCommand = ocmd;
DataRow[] updRows = CLOBInfo.Tables[_TableName].Select(null, null, DataViewRowState.Added);
if (updRows.Length > 0)
{
int rc = oda.Update(updRows);
CLOBInfo.AcceptChanges();
AddLogMessage("Row inserted into CLOBTEST. rc = " + rc.ToString());
}
else
AddLogMessage("No rows to insert.");
}
}
}
}
private void UpdateRow(DataSet CLOBInfo)
{
string SQL = "UPDATE " + _TableName + " SET ACTION_CODE = :pACTION_CODE, DESCRIPTION = :pDESCRIPTION, SCRIPT_TEXT = :pSCRIPT_TEXT WHERE ACTION_CODE = :pOLDACTION_CODE";
using (OracleConnection oc = OpenConnection())
{
using (OracleDataAdapter oda = new OracleDataAdapter())
{
using (OracleCommand ocmd = oc.CreateCommand())
{
ocmd.CommandText = SQL;
CreateDataParameters(ocmd);
OracleParameter kp = new OracleParameter();
kp.ParameterName = "pOLDACTION_CODE";
kp.SourceColumn = "ACTION_CODE";
kp.SourceVersion = DataRowVersion.Original;
ocmd.Parameters.Add(kp);
oda.UpdateCommand = ocmd;
DataRow[] updRows = CLOBInfo.Tables[_TableName].Select(null, null, DataViewRowState.ModifiedCurrent);
if (updRows.Length > 0)
{
int rc = oda.Update(updRows);
CLOBInfo.AcceptChanges();
AddLogMessage("CLOBTEST row updated. rc = " + rc.ToString());
}
else
AddLogMessage("No rows to update.");
}
}
}
}
private void CreateDataParameters(OracleCommand ocmd)
{
OracleParameter pActionCode = new OracleParameter();
pActionCode.ParameterName = "pACTION_CODE";
pActionCode.SourceColumn = "ACTION_CODE";
ocmd.Parameters.Add(pActionCode);
OracleParameter pDescription = new OracleParameter();
pDescription.ParameterName = "pDESCRIPTION";
pDescription.SourceColumn = "DESCRIPTION";
ocmd.Parameters.Add(pDescription);
OracleParameter pScriptText = new OracleParameter();
pScriptText.ParameterName = "pSCRIPT_TEXT";
pScriptText.SourceColumn = "SCRIPT_TEXT";
ocmd.Parameters.Add(pScriptText);
}
private DataSet BuildCLOBTestDataSet()
{
DataSet ads = new DataSet();
DataTable at = new DataTable("CLOBTEST");
DataColumn ac = at.Columns.Add("ACTION_CODE", typeof(string));
at.Columns.Add("DESCRIPTION", typeof(string));
at.Columns.Add("SCRIPT_TEXT", typeof(string));
at.PrimaryKey = new DataColumn[] { ac };
ads.Tables.Add(at);
return ads;
}
private OracleConnection OpenConnection()
{
OracleConnection oc = null;
try
{
OracleClientFactory ocf = new OracleClientFactory();
DbConnectionStringBuilder csb = ocf.CreateConnectionStringBuilder();
csb["Data Source"] = _ServerName;
csb["User ID"] = _UserName;
csb["Password"] = _Password;
string cs = csb.ConnectionString;
oc = new OracleConnection(cs);
oc.Open();
AddLogMessage("Connection opened.");
}
catch (Exception ex)
{
AddLogMessage("Error Opening Connection! " + ex.Message);
throw;
}
return oc;
}
private void AddLogMessage(string msg)
{
Debug.WriteLine(string.Format("{0:T} - {1}", DateTime.Now, msg));
}
}
最佳答案
此线程中显示了两种解决方法：https://community.oracle.com/thread/3649551
解决方法一：
将CLOB参数配置为OracleDbType.Clob和ParameterDirection.InputOutput。
解决方法二：
将CLOB参数配置为OracleDbType.Clob，并将其值显式设置为OracleClob对象。
固定：
该错误现在对于Oracle来说是已知的，因此希望可以很快发布一个固定版本。
编辑：Patch 20361140应该修复它。
关于c# - 具有受管ODP.Net的ORA-1461，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/28378048/