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.

Keitloa

You should use NTEXT for sql server, NVARCHAR(MAX) will likely not handle more than 4000 chars. – Reichard

@YavorShahpasov why would you think that NVARCHAR(MAX) will not handle more than 4000 characters? The docs say "max indicates that the maximum storage size is 2^31-1 bytes (2 GB)." That should be enough for 1073741823 characters or so. – Bushido

I was misled by the fact that it allows 1-4000 chars. You are correct and max will allow upto 2GB. – Reichard

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

Reichard

Answer 2

For whom this may interest, I solved my problem following the step 3 of this article:

3. Using correct Mapping attributes: type="AnsiString"

Normally we can use type="String" default for CLOB/NCLOB. Try to use > type="AnsiString" if two steps above not work.

<property name="SoNhaDuongPho" column="SO_NHA_DUONG_PHO" type="AnsiString"/>

In my case I set it with FluentNHibernate:

.CustomType("AnsiString")

Thielen

Answer 3

You might be interested in this article.

<property column="`LARGE_STRING`" name="LargeString" type="StringClob" sql-type="NCLOB" />

Dispersant