Hi all,

Peculiar problem with NHibernate updating morethan 2000 characters in OracleDatabase 10g.

I am using Varchar(4000) for this field. In HBM file, corresponding DATATYE is STRING. Pls refer the Mapping file atatched.

If i insert the morethan 2000 characters in PL/SQL it is accepting.
Please help me on this.


Hibernate version:

1.2

Mapping documents:
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="ApplicationFramework.BE.Rule, ApplicationFramework" table="CM_RULE">
<id name="RuleId" type="Int32" unsaved-value="null">
<column name="RULE_ID" length="4" sql-type="int" not-null="true" />
<generator class="assigned" />
</id>
<property name="CaseId" type="Int32">
<column name="CASE_ID" length="4" sql-type="int" not-null="true" />
</property>
<property name="TaskId" type="Int32">
<column name="TASK_ID" length="4" sql-type="int" not-null="true" />
</property>
<property name="RuleCategoryId" type="Int32">
<column name="RULE_CATEGORY_ID" length="4" sql-type="int" not-null="true" />
</property>
<property name="RuleDescription" type="String">
<column name="RULE_DESCRIPTION" length="1000" sql-type="varchar" not-null="false" />
</property>
<property name="RuleComments" type="String">
<column name="RULE_COMMENTS" length="500" sql-type="varchar" not-null="false" />
</property>
<property name="RuleKey" type="Int32">
<column name="RULE_KEY" length="4" sql-type="int" not-null="true" />
</property>
<property name="ProseText" type="String">
<column name="PROSE_TEXT" length="4000" sql-type="varchar" not-null="false" />
</property>
<property name="PartialRule" type="Int32">
<column name="PARTIAL_RULE" length="4" sql-type="int" not-null="false" />
</property>
<property name="RuleStandardNotice" type="String">
<column name="RULE_STANDARD_NOTICE" length="1000" sql-type="varchar" not-null="false" />
</property>
<property name="RuleTypeId" type="Int32">
<column name="RULE_TYPE_ID" length="4" sql-type="int" not-null="false" />
</property>
<property name="SortOrder" type="Int32">
<column name="SORT_ORDER" length="4" sql-type="int" not-null="true" />
</property>
<property name="RuleDisposition" type="Int32">
<column name="RULE_DISPOSITION" length="4" sql-type="int" not-null="true" />
</property>
<property name="RxOnlyFlag" type="Int32">
<column name="RX_ONLY_FLAG" length="4" sql-type="int" not-null="true" />
</property>
<property name="ConditionConfirmation" type="Int32">
<column name="CONDITION_CONFIRMATION" length="4" sql-type="int" not-null="false" />
</property>
<property name="ClinicalConcept" type="String">
<column name="CLINICAL_CONCEPT" length="1000" sql-type="varchar" not-null="false" />
</property>
<property name="BasedOn" type="String">
<column name="BASED_ON" length="200" sql-type="varchar" not-null="false" />
</property>
<property name="CreateDate" type="DateTime">
<column name="CREATE_DATE" length="8" sql-type="datetime" not-null="true" />
</property>
<property name="ModifyDate" type="DateTime">
<column name="MODIFY_DATE" length="8" sql-type="datetime" not-null="true" />
</property>
<property name="CreatedBy" type="String">
<column name="CREATED_BY" length="50" sql-type="varchar" not-null="true" />
</property>
<property name="ModifiedBy" type="String">
<column name="MODIFIED_BY" length="50" sql-type="varchar" not-null="true" />
</property>
<property name="UserType" type="String">
<column name="USER_TYPE" length="50" sql-type="varchar" not-null="true" />
</property>
<many-to-one name="Task" class="ApplicationFramework.BE.Task, ApplicationFramework" lazy="false" insert="false" update="false">
<column name="TASK_ID" length="4" sql-type="int" not-null="true" />
</many-to-one>
<many-to-one name="Case" class="ApplicationFramework.BE.Case, ApplicationFramework" lazy="false" insert="false" update="false">
<column name="CASE_ID" length="4" sql-type="int" not-null="true" />
</many-to-one>
<many-to-one name="RuleCategory" class="ApplicationFramework.BE.RuleCategory, ApplicationFramework" lazy="false" insert="false" update="false">
<column name="RULE_CATEGORY_ID" length="4" sql-type="int" not-null="true" />
</many-to-one>
<many-to-one name="RuleType" class="ApplicationFramework.BE.RuleType, ApplicationFramework" lazy="false" insert="false" update="false">
<column name="RULE_TYPE_ID" length="4" sql-type="int" not-null="false" />
</many-to-one>
<bag name="Statements" inverse="true" lazy="true" cascade="all-delete-orphan">
<key column="RULE_ID" />
<one-to-many class="ApplicationFramework.BE.Statement, ApplicationFramework" />
</bag>
</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

ERROR 2007-11-23 17:00:28,085 51936ms - An exception of type 'NHibernate.ADOException' occurred and was caught.
-----------------------------------------------------------------------
Date & Time: 11/23/2007 17:00:27
Type:NHibernate.ADOException, NHibernate, Version=1.2.0.4000, Culture=neutral, PublicKeyToken=aa95f207798dfdb4
Message : could not execute query
[ select refinement0_.REFINEMENT_ID as REFINEMENT1_8_, refinement0_.REFINEMENT_NAME as REFINEMENT2_8_, refinement0_.REFINEMENT_DESCRIPTION as REFINEMENT3_8_, refinement0_.PROSE_TEXT as PROSE4_8_, refinement0_.CREATE_DATE as CREATE5_8_, refinement0_.MODIFY_DATE as MODIFY6_8_, refinement0_.CREATED_BY as CREATED7_8_, refinement0_.MODIFIED_BY as MODIFIED8_8_, refinement0_.USER_TYPE as USER9_8_ from EM_REFINEMENT refinement0_ where (refinement0_.REFINEMENT_ID=) ]
[SQL: select refinement0_.REFINEMENT_ID as REFINEMENT1_8_, refinement0_.REFINEMENT_NAME as REFINEMENT2_8_, refinement0_.REFINEMENT_DESCRIPTION as REFINEMENT3_8_, refinement0_.PROSE_TEXT as PROSE4_8_, refinement0_.CREATE_DATE as CREATE5_8_, refinement0_.MODIFY_DATE as MODIFY6_8_, refinement0_.CREATED_BY as CREATED7_8_, refinement0_.MODIFIED_BY as MODIFIED8_8_, refinement0_.USER_TYPE as USER9_8_ from EM_REFINEMENT refinement0_ where (refinement0_.REFINEMENT_ID=)]
Source : NHibernate
Help link :
SqlString : select refinement0_.REFINEMENT_ID as REFINEMENT1_8_, refinement0_.REFINEMENT_NAME as REFINEMENT2_8_, refinement0_.REFINEMENT_DESCRIPTION as REFINEMENT3_8_, refinement0_.PROSE_TEXT as PROSE4_8_, refinement0_.CREATE_DATE as CREATE5_8_, refinement0_.MODIFY_DATE as MODIFY6_8_, refinement0_.CREATED_BY as CREATED7_8_, refinement0_.MODIFIED_BY as MODIFIED8_8_, refinement0_.USER_TYPE as USER9_8_ from EM_REFINEMENT refinement0_ where (refinement0_.REFINEMENT_ID=)
Data : System.Collections.ListDictionaryInternal
TargetSite : System.Collections.IList DoList(NHibernate.Engine.ISessionImplementor, NHibernate.Engine.QueryParameters)
Stack Trace : at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
at NHibernate.Hql.Classic.QueryTranslator.List(ISessionImplementor session, QueryParameters queryParameters)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters, IList results)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters)
at NHibernate.Impl.QueryImpl.List()
at ApplicationFramework.DAL.Bases.RefinementDALBase.Get(String query, RefParam`1 collection) in D:\Check In\dotnet\HCRM\src\Components\ApplicationFramework\ApplicationFramework\DAL\Bases\RefinementDALBase.cs:line 303

Additional Info:

MachineName : PC008635
TimeStamp : 11/23/2007 11:30:28 AM
FullName : .CAFE.FoundationServices.ExceptionManagement, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null
AppDomainName : HCRM.vshost.exe
ThreadIdentity :
WindowsIdentity : CTS\133621

Inner Exception
---------------
Type:System.Data.OracleClient.OracleException, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : ORA-00936: missing expression

Source : System.Data.OracleClient
Help link :
Code : 936
ErrorCode : -2146232008
Data : System.Collections.ListDictionaryInternal
TargetSite : Void CheckError(System.Data.OracleClient.OciErrorHandle, Int32)
Stack Trace : at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at NHibernate.Impl.BatcherImpl.ExecuteReader(IDbCommand cmd)
at NHibernate.Loader.Loader.GetResultSet(IDbCommand st, RowSelection selection, ISessionImplementor session)
at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)


ERROR 2007-11-23 17:00:31,194 55045ms - An exception of type 'NHibernate.ADOException' occurred and was caught.
-----------------------------------------------------------------------
Date & Time: 11/23/2007 17:00:31
Type:NHibernate.ADOException, NHibernate, Version=1.2.0.4000, Culture=neutral, PublicKeyToken=aa95f207798dfdb4
Message : could not update: [ApplicationFramework.BE.Rule#800073][SQL: UPDATE CM_RULE SET CASE_ID = ?, TASK_ID = ?, RULE_CATEGORY_ID = ?, RULE_DESCRIPTION = ?, RULE_COMMENTS = ?, RULE_KEY = ?, PROSE_TEXT = ?, PARTIAL_RULE = ?, RULE_STANDARD_NOTICE = ?, RULE_TYPE_ID = ?, SORT_ORDER = ?, RULE_DISPOSITION = ?, RX_ONLY_FLAG = ?, CONDITION_CONFIRMATION = ?, CLINICAL_CONCEPT = ?, BASED_ON = ?, CREATE_DATE = ?, MODIFY_DATE = ?, CREATED_BY = ?, MODIFIED_BY = ?, USER_TYPE = ? WHERE RULE_ID = ?]
Source : ApplicationFramework
Help link :
SqlString : UPDATE CM_RULE SET CASE_ID = ?, TASK_ID = ?, RULE_CATEGORY_ID = ?, RULE_DESCRIPTION = ?, RULE_COMMENTS = ?, RULE_KEY = ?, PROSE_TEXT = ?, PARTIAL_RULE = ?, RULE_STANDARD_NOTICE = ?, RULE_TYPE_ID = ?, SORT_ORDER = ?, RULE_DISPOSITION = ?, RX_ONLY_FLAG = ?, CONDITION_CONFIRMATION = ?, CLINICAL_CONCEPT = ?, BASED_ON = ?, CREATE_DATE = ?, MODIFY_DATE = ?, CREATED_BY = ?, MODIFIED_BY = ?, USER_TYPE = ? WHERE RULE_ID = ?
Data : System.Collections.ListDictionaryInternal
TargetSite : Void CommitTransaction()
Stack Trace : at ApplicationFramework.DAL.Core.DALBase.CommitTransaction() in D:\Check In\dotnet\HCRM\src\Components\ApplicationFramework\ApplicationFramework\DAL\Core\DALBase.cs:line 143
at ApplicationFramework.DAL.Bases.RuleDALBase.Update(Rule objRule) in D:\Check In\dotnet\HCRM\src\Components\ApplicationFramework\ApplicationFramework\DAL\Bases\RuleDALBase.cs:line 509

Additional Info:

MachineName : PC008635
TimeStamp : 11/23/2007 11:30:31 AM
FullName : .CAFE.FoundationServices.ExceptionManagement, Version=2.0.0.0, Culture=neutral, PublicKeyToken=null
AppDomainName : HCRM.vshost.exe
ThreadIdentity :
WindowsIdentity : CTS\133621

Inner Exception
---------------
Type:System.Data.OracleClient.OracleException, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : ORA-01461: can bind a LONG value only for insert into a LONG column

Source : System.Data.OracleClient
Help link :
Code : 1461
ErrorCode : -2146232008
Data : System.Collections.ListDictionaryInternal
TargetSite : Void CheckError(System.Data.OracleClient.OciErrorHandle, Int32)
Stack Trace : at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.Impl.NonBatchingBatcher.AddToBatch(IExpectation expectation)
at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Object[] oldFields, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session)

Name and version of the database you are using:

Oracle 10g

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html

Hi All,

This bug is fixed.
When i debug into the Nhibernate Source, we found the issue in mapping datatype.

This is the mapping HBM attribute:
<property name="PText" type="String">
<column name="PText" length="4000" sql-type="varchar" not-null="false" />
</property>
In the GenericDialect class of NHibernate Source, NHibernate is registering as NVarchar for eqivalent datatype for STRING, even though we pass it as "VARCHAR" Datatype in our HBM file.

Because of this, Varchar has now become NVarchar, as a result
ERROR "ORA-01461: can bind a LONG value only for insert into a LONG column ", we got this ERROR.

In order to fix this, I changed the mapping attribute as follow
<property name="PText" type="AnsiString">
<column name="PText" length="4000" sql-type="varchar" not-null="false" />
</property>


And yes, I was able to fix the above issue.

Thank you all buddies for your valuable time.
Regards
Sudhagar.S

Hello sudhagar,

I work seam, hibernate, ejb3 and j2ee. Where i change ??

I have field varchar2(4000) and get ERROR "ORA-01461: can bind a LONG value only for insert into a LONG column " when i try insert a String (java) with 2000 bytes or more.

Regards :)

ups, I not use NHibernate :/, i use Hibernate :)

Sorry.

Source : NHibernate
Help link :
SqlString : select refinement0_.REFINEMENT_ID as REFINEMENT1_8_, refinement0_.REFINEMENT_NAME as REFINEMENT2_8_, refinement0_.REFINEMENT_DESCRIPTION as REFINEMENT3_8_, refinement0_.PROSE_TEXT as PROSE4_8_, refinement0_.CREATE_DATE as CREATE5_8_, refinement0_.MODIFY_DATE as MODIFY6_8_, refinement0_.CREATED_BY as CREATED7_8_, refinement0_.MODIFIED_BY as MODIFIED8_8_, refinement0_.USER_TYPE as USER9_8_ from EM_REFINEMENT refinement0_ where (refinement0_.REFINEMENT_ID=)
Data : System.Collections.ListDictionaryInternal
TargetSite : System.Collections.IList DoList(NHibernate.Engine.ISessionImplementor, NHibernate.Engine.QueryParameters)

This fix saved my bacon! Thanks a million!!