Mapping a long text string in Oracle and NHibernate

oracle nhibernate text clob lob
brianberns picture brianberns · Aug 16, 2011 · Viewed 7.3k times · Source

Using NHibernate 3.1 with both SQL Server and Oracle DBs, we need to store a text string that is longer than 4000 characters. The text is actually XML, but that is not important - we just want to treat it as raw text. With SQL Server, this is easy. We declare the column as NVARCHAR(MAX) and map it thusly:

<property name="MyLongTextValue" length="100000"/>

The use of the length property tells NHibernate to expect a string that may be longer than 4000 characters.

For the life of me, I cannot figure out how to make this work on Oracle 11g. I've tried declaring the column as both XMLTYPE and LONG with no success. In the first case, we end up with ORA-01461: can bind a LONG value only for insert into a LONG column when trying to insert a row. In the second case, the data is inserted correctly but comes back as an empty string when querying.

Does anyone know how to make this work? The answer has to be compatible with both SQL Server and Oracle. I'd rather not have to write custom extensions such as user types and driver subclasses. Thanks.

Answer

Yavor Shahpasov picture Yavor Shahpasov · Aug 16, 2011

You should use something like this

<property name="MyLongTextValue" length="100000" type="StringClob" 
not-null="false"/>

This should work with Oracle CLOB type and SqlServer NTEXT type.

Make sure the property on your model is nullable

public virtual string MyLongTextValue {get;set;}

You should always use the Oracle.DataAccess when dealing with CLOBs

Related questions

Which .NET data type is best for mapping the NUMBER Oracle data type in NHibernate?

I've seen some examples in which decimal is used in NHibernate projects for mapping to whole number columns in Oracle. Right now I'm using int and long in my program. What are the advantages of decimal over int/long? Does …

Read More
Convert from Oracle's RAW(16) to .NET's GUID

I'm having difficulties manually debugging an .NET application where the Guid values differ from .NET to Oracle. Where C# reads: 17D89D326C2142D69B989F5201288DBF Oracle reads: 329DD817216CD6429B989F5201288DBF How would I be able to …

Read More
Could not create the driver from NHibernate.Driver.OracleDataClientDriver

Here's the code raising the exception: public static class NHibernateSessionManager { private static ISessionFactory sessionFactory = new Configuration().Configure().BuildSessionFactory(); public static ISession GetSession(string clientId) { if (ContextSession == null) ContextSession = sessionFactory.OpenSession(new OracleIntercerptor(clientId.ToUpper())); else ((OracleConnection)ContextSession.Connection).ClientId = clientId; …

Read More