CODEWITHSUNDEEP
oracle-databasenhibernatetextcloblob
Brian Berns
Brian Berns

Reputation: 17153

Mapping a long text string in Oracle and NHibernate

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.

Upvotes: 5

Views: 8136

Answers (3)

Yavor S
Yavor S

Reputation: 1493

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

Upvotes: 7

DontVoteMeDown
DontVoteMeDown

Reputation: 21475

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")

Upvotes: 2

LeftyX
LeftyX

Reputation: 35597

You might be interested in this article.

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

Upvotes: 0

Related Questions