Mapping a long text string in Oracle and NHibernate

Question

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 1

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