title: Using ODP.Net with NHibernate with .net System.decimal
body: <p>I am using ODP.net to run the aggregate AVG against an Oracle 10g database.  I run this query directly on the database and it works fine:</p>
  
  <p>select avg(ANSCHAFFUNGSKST) from IHObjekt</p>
  
  <p>it returns:  13.4493973163521</p>
  
  <p>Niether the HQL nor the CreateCriteria interfaces successfully execute the query.  I recieve an NHibernate 'could not execute query' message.  However, I am relatively certain it is an ODP.Net error based on this <a href="http://forums.oracle.com/forums/thread.jspa?threadID=231945&amp;tstart=5460" rel="nofollow">posting</a>.</p>
  
  <p>There is a solution for Oracle, simply TRUNC the AVG.  However, the TRUNC command is different in Oracle versus SQL Server and I need/want to keep my code from being database specific.</p>
  
  <p>Any ideas about how I can reduce the number of decimal points so it fits within a decimal and most importantly, it works on all databases?</p>
  
  <h1>Source =  NHibernate</h1>
  
  <h2>StackTrace</h2>
  
  <ul>
  <li>NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)</li>
  <li>NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)</li>
  <li>NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes)</li>
  <li>NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters)</li>
  <li>NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters)</li>
  <li>NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results)</li>
  <li>NHibernate.Impl.SessionImpl.List(String query, QueryParameters queryParameters, IList results)</li>
  <li>NHibernate.Impl.SessionImpl.List(String query, QueryParameters parameters)</li>
  <li>NHibernate.Impl.QueryImpl.List()</li>
  <li>DBTest_NHibernate.MainWindow.ButtonHQLQuery_Click(Object sender, RoutedEventArgs e) in C:\...</li>
  </ul>
  
  <h2>InnerException</h2>
  
  <p>[System.OverflowException] = {"Die arithmetische Operation hat einen Überlauf verursacht."} ... The arithmetic operation has caused an overflow.</p>
  
  <h1>Source = Oracle.DataAccess</h1>
  
  <h2>StackTrace</h2>
  
  <ul>
  <li>Oracle.DataAccess.Types.DecimalConv.GetDecimal(IntPtr numCtx)</li>
  <li>Oracle.DataAccess.Client.OracleDataReader.GetDecimal(Int32 i)</li>
  <li>Oracle.DataAccess.Client.OracleDataReader.GetValue(Int32 i)</li>
  <li>Oracle.DataAccess.Client.OracleDataReader.get_Item(Int32 i)</li>
  <li>NHibernate.Type.DoubleType.Get(IDataReader rs, Int32 index)</li>
  <li>NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)</li>
  <li>NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner)</li>
  <li>NHibernate.Hql.Ast.ANTLR.Loader.QueryLoader.GetResultColumnOrRow(Object[] row, IResultTransformer resultTransformer, IDataReader rs, ISessionImplementor session)</li>
  <li>NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies)</li>
  <li>NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)</li>
  <li>NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)</li>
  <li>NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)</li>
  </ul>
  
  <h2>Some HQL Testing Results</h2>
  
  <ul>
  <li>select  TRUNC(AVG(ANSCHAFFUNGSKST),27) from IHObjekt - WORKS (ONLY IN ORACLE)</li>
  <li>select  TRUNC(AVG(ANSCHAFFUNGSKST),28) from IHObjekt - DOES NOT WORK</li>
  <li>select  AVG(ANSCHAFFUNGSKST) from IHObjekt - DOES NOT WORK</li>
  </ul>
  
  <h1>NHibernate Generated SQL</h1>
  
  <pre><code>SELECT
      AVG(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_0_0_,
      COUNT(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_1_0_,
      MAX(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_2_0_,
      MIN(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_3_0_,
      SUM(IHOBJEKT0_.ANSCHAFFUNGSKST) AS COL_4_0_ 
  FROM
      IHOBJEKT IHOBJEKT0_
  </code></pre>
  
  <p>Only AVG does not work in the above SQL statement on Oracle using ODP.Net.  Using SQL Server or the Oracle client it works.</p>