Long strings in N-Hibernate with Oracle cause error

Question

I use oracle as db and free Nhibernate for display.

Below is my feature class

public class UserFieldEvent { public virtual int Id { get; set; } public virtual UserFieldBase UserField { get; set; } public virtual EventType EventType { get; set; } public virtual string EventScript { get; set; } } 

The length of the EventScript property can be from 0 to 4000. In the database, I made a column type for the EventScript CLOB.

Below is my mapping class:

 public UserFieldEventMap() { Table("TBLDS_USERFIELDEVENT"); Id(x => x.Id).GeneratedBy.Sequence("SEQDS_USERFIELDEVENT"); Map(x => x.EventType).CustomType<EventType>(); Map(x => x.EventScript).CustomSqlType("CLOB"); References(x => x.UserField).Column("USERFIELDBASEID"); } 

Now that the EventScript is longer than 2000, I get the error message "ORA-01461: may bind the LONG value only for insertion into the LONG column". storing the object in the database. Can anyone help with this.

Answer 1

This is a known issue with .NET provided by System.Data.OracleClient.OracleConnection . The fix is ​​either to use the Oracle.DDPAccess.Client.OracleConnection provided by Oracle ODP.net client (see http://nuget.org/packages/odp.net.x86/ ), or use the following workaround (link to: http : //thebasilet.blogspot.be/2009/07/nhibernate-oracle-clobs.html ).

 public class CustomOracleDriver : OracleClientDriver { protected override void InitializeParameter(System.Data.IDbDataParameter dbParam, string name, SqlType sqlType) { base.InitializeParameter(dbParam, name, sqlType); // System.Data.OracleClient.dll driver generates an ORA-01461 exception because // the driver mistakenly infers the column type of the string being saved, and // tries forcing the server to update a LONG value into a CLOB/NCLOB column type. // The reason for the incorrect behavior is even more obscure and only happens // when all the following conditions are met. // 1.) IDbDataParameter.Value = (string whose length: 4000 > length > 2000 ) // 2.) IDbDataParameter.DbType = DbType.String // 3.) DB Column is of type NCLOB/CLOB // The above is the default behavior for NHibernate.OracleClientDriver // So we use the built-in StringClobSqlType to tell the driver to use the NClob Oracle type // This will work for both NCLOB/CLOBs without issues. // Mapping file must be updated to use StringClob as the property type // See: http://thebasilet.blogspot.be/2009/07/nhibernate-oracle-clobs.html if ((sqlType is StringClobSqlType)) { ((OracleParameter)dbParam).OracleType = OracleType.NClob; } } } 

You need to update your SessionFactory to use this driver, as well as update any of your clone mappings to use a non-standard StringClob type

 Map(x => x.EventType).CustomSqlType("Clob").CustomType("StringClob"); 

Answer 2

The one who is still looking

Have a look at this article for SQL SERVER

https://gist.github.com/phillip-haydon/1936188

and this time for PostgreSql

https://gist.github.com/bariloce/e65fe5db6c6ddf46e6f8

Define a user user type first AsBlobStringUserType as shown in the articles

In the custom type, specify SqlType as StringClobSqlType

  SqlType[] SqlTypes { get => new SqlType[] { new StringClobSqlType() }; } 

And then use as

  mapping .Map(member => member.PropertyToMapName) .CustomType<Blobbed<PropertyToMapClass>>();