C# .NET - Inserting long string (> 2000) in Oracle gives ORA-01460

Issue: I get

System.Data.OracleClient.OracleException was unhandled by user code
  Message=ORA-01460: unimplemented or unreasonable conversion requested

  Source=System.Data.OracleClient
  ErrorCode=-2146232008
  Code=1460
  StackTrace:
     at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
     at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
     at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
     at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
     at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteNonQuery(DbCommand command)
     at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteNonQuery(DbCommand command)

I am trying to insert a long string (varchar2) (> 2000 in length). I am using a stored procedure to do this. Any string where length < 2000 is ok. I am not using any CLOB or LOB or BLOB. when i call the insert using the entlib data block, i get the above error.
If I call the stored procedure directly through SQL developer, any string > 2000 also works. I think the issue is with System.Data.OracleClient. any ideas/pointers will be appreciated.

try this

This problem occurs when you use the Microsoft Oracle provider and passing more than 32k of data to a CLOB parameter of an Oracle Stored Procedure.

There are a couple of ways to work around this problem:

1) Instead of calling a stored procedure to insert into a clob field, use inline SQL to insert/update the field

2) Rewrite your logic to use Oracle Data Provider for .NET (ODP.NET) version 9.2, here is a sample code

 In your project, you would need to reference the following dll: Oracle.DataAccess.dll

VB.NET

Imports Oracle.DataAccess.Client

Dim conn As OracleConnectionDim cmd As OracleCommand

conn = New OracleConnection("Database Connection String")
cmd = New OracleCommand("Stored Procedure Name", conn)

cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("ClobData", OracleDbType.Clob, CLOB_DATA, ParameterDirection.Input)

conn.Open()
cmd.ExecuteNonQuery()

cmd.Dispose()
conn.Close()

also see this sample is working very good
string indata = new string('a', 1000000);
      Console.WriteLine("input string length is {0}", indata.Length);
 
      // insert into database using (OracleConnection con = new OracleConnection("user id=scott;password=tiger;data source=orcl"))
      {
        con.Open();
        using (OracleCommand cmd = new OracleCommand())
        {
          cmd.CommandText = "insert into testclobtab values(1,:clobparam)";
          cmd.Connection = con;
          OracleClob oc = new OracleClob(con);
          for (int i = 0; i < 500; i++)
            oc.Append(indata.ToCharArray(), 0, indata.Length);
          OracleParameter clobparam = new OracleParameter("clobparam", OracleDbType.Clob, indata.Length);
          clobparam.Direction = ParameterDirection.Input;
          clobparam.Value = oc;
          cmd.Parameters.Add(clobparam);
          cmd.ExecuteNonQuery();
          Console.WriteLine("insert complete");
          clobparam.Dispose();
          oc.Dispose();
        }
      }

web star: thanks for the response. But I am not working with CLOB. I need to work with strings on the .NET side and VARCHAR2's on the oracle side. Following is a sample of the code:

public override bool Insert(TransactionManager transactionManager, COMMENTS entity)
{
   OracleDatabase database = new OracleDatabase(this._connectionString);
   DbCommand commandWrapper = StoredProcedureProvider.GetCommandWrapper(database, "COMMENTS.P_Insert",    _useStoredProcedure);
 

 // adding other parameters --code removed.
 // adding the string parameter. if entity.comments.length = 2000, insert is ok, if > 2000, it fails with above error.
 database.AddInParameter(commandWrapper, "COMMENTS", DbType.String, entity.COMMENTS );
 int results = 0;

 results = database.ExecuteNonQuery(dbCommand);
 return results;
}
  

Thanks.

There is no CLOB involved in the picture. my DB type is VARCHAR2 and i am passing a string. and the size is not anywhere near 32K its just 2000 characters.

Was this issue ever resolved because I'm having the same problem?  Thanks.