Recently I was in fight with an ORACLE “issue” I found in my day-by-day coding activity. I was lucky to discover this “issue”, after 2 hours spent in debugging, rewriting mappings, and some bad words I said to my computer, I found a solution and a post which was helpful for me.

The situation: I was mapping a simple, usual, set of Entity Properties to a related set of ORACLE NUMBER columns with no precision info. These columns were updated by a Package Procedure. So, assuming Column1 and Column2 were the comlumns I was trying to map, I mapped them in a standard way as:

   1: <!-- ... -->
   2: <property name="PropertyOne"
   3:           type="System.Decimal">
   4:   <column name="COLUMN1"/>
   5: </property>
   6: <property name="PropertyTwo"
   7:           type="System.Decimal">
   8:   <column name="COLUMN2"/>
   9: </property>
  10: <!-- ... -->

I though this could be a very easy and standard mapping, so I didnt take  so much care about, because this was a usual mapping, but I was wrong (maybe I wasn’t but Oracle were)

The problem: After I’ve done with the mapping above, I experimented a strange kind of Exception, this Exception was so strange I fall in a desperate search of all possible causes, but with no asnwers. The Exception was “Arithmetic operation resulted in an overflow“.

Now, no spending more words about my search for a cause, This situation makes me crazy, what could be the problem I asked to me? Then, as a last try, I’ve done the same operation I was doing with NHibernate in the old ADO.NET – DataSet way, and here the surprise: I FOUND THE SAME ERROR!! How was it possible, a column of NUMBER, with no precision (this assume the biggest precision supported, I though) fall in an Exception during reading from?

So after some search in the Google universe, I found a possible solution, and when I read about, it makes me too much crazy than before. I found a post in the Oracle Forum which describe a similar situation with the same exception, the answer says something like:

..We found that this is caused by a return value that has more places to the right of the decimal point than .NET can handle…Be careful of functions that return calculated values. They somethimes try to return a larger number than even ODP.NET can handle.

The solution: After days, I found this a ridiculous thing, why this could happen? Trying to better explain what above sayd, the problem was with CALCULATED VALUES maybe some times a calculated decimal can result in a decimal point number too bigger than a standard .NET decimal point number, so, it’s clear that I felt in this kind of exception, and NHibernate was innocent, because the underlying ODP.NET was the problem. I don’t want to spend much words, (I wrote more than I’ll wish), following the suggestion, this kind of bug could be solved in no many ways:

  1. change the mapping to use with a VIEW with truncated values, but I don’t like to intriduce more objects than it’s needed
  2. change the mapping to truncate values, but this fall into a “broken mapping”, a mapping which target to a single Database implementation, in my case Oracle
  3. change the procedure to outputs a well delimited  NUMBER value, but this wasn’t possible because this part of the application wasn’t written and controlled by me

So I decided for second way, in this way, just change the mapping as I will show, my code and my mapping go ahead without exceptions and any kind of problem, here is the modified mapping:

   1: <!-- ... -->
   2: <property name="PropertyOne"
   3:           type="Decimal"
   4:           formula="TRUNC(COLUMN1, 6)" />
   5: <property name="PropertyTwo"
   6:           type="Decimal"
   7:           formula="TRUNC(COLUMN2, 6)" />
   8: <!-- ... -->

In this way the resulting query result with the TRUNC function which applied as above truncate the decimal places as a desired, fixed number.

I hope this helps and could prevent anyone to a “crazy-noon” as does! ah… here is the Oracle Forum Post which gracefully helps me Arithmetic opreation…POST

Sorry for my poor and bad English, I hope I will learn well..Ciao!

Technorati Tag: ,,

Hi, recently I read many requests about how to map an Oracle RAW() Type. About 2 months ago, I was one of the those who was asking for a solution, I really cannot believe how could be possible there is not a solution, so searching in the Net I found 3 kind of solution, then, I decided what was the better for me and so I’ve implemented in my project, let’s begin.

First of all, in general a RAW column data type describe a raw binary data, generally describing a Guid. Even generally, when using a RAW data type mean, as .NET world, using a GUID, so our need is to use a RAW as GUID.

Normally there are 2 modes of reading a RAW data type, in the ADO.NET way, from a command:

  • if not is specified the RAW column returns as a Byte[] array
  • if specified, it’s possible to use during reading the RAWTOHEX(columnName) that converts from a raw byte array to a binary string, and vice versa during writing the HEXTORAW(columnName) that converts back from a binary string to a raw byte array.

but what if we want to map that column data type in a HBM mapping? Well, there are 3 ways (usually I use the last one):

  • Creating a custom NHibernate Dialect inheriting from the OracleDialect and map the Guid data type so the convertion is made while reading clearly by the engine
  • Specify a “formula” surrounding the column name and decorate the column name with the RAWTOHEX function, bu the problem came up when we want to write and we should use the HEXTORAW function
  • Creating a custom IUserType that converts to and from the RAW type.

As I said before I prefer the last one, so here is my solution: (here i will discuss the code in pieces, then in the end of the post is available the code to download)

Firts of all, we start creating a class named (in my case) RawType:

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Data;
   4: using System.Text;
   5: using NHibernate;
   6: using NHibernate.SqlTypes;
   7: using NHibernate.UserTypes;
   9: namespace MySolution.Data.Types
  10: {
  11:     public class RawType : IUserType
  12:     {

The class must inherits from IUserType, this interface instructs NHibernate we want map a specifed database column name with a custom type. Next, to correctly implement the type we need to specify the NHibernate SqlType represented by our custom type, I used the DbType.Binary in a static SqlType array variable as:

   1: private static SqlType[] types =
   2:     new SqlType[] { new SqlType(DbType.Binary) };

Next the core part of the implementation are the methods NullSafeGet and NullSafeSet that are used to handle the “raw ado read value” and translate it depending if we are reading or writing. In order, the implementation handles this situation as follow:

Reading – the value is converted from the raw binary data to System.String, this is done using the System.Guid structure that permit to create a Guid from binary array assuming a valid 32 byte array, the the ToString method with the “N” format creates a guid strings without wpecial characters

Writing – the value is converted from System.String to a raw binary representation, this is yet done using the System.Guid structure ToByteArray method

   1: public object NullSafeGet(IDataReader rs, string[] names, object owner)
   2: {
   3:     string result = null;
   4:     byte[] buffer = (byte[])NHibernateUtil.Binary.NullSafeGet(rs, names[0]);
   5:     if (null != buffer)
   6:     {
   7:         result = new Guid(buffer).ToString("N");
   8:         Array.Clear(buffer, 0, buffer.Length);
   9:     }
  10:     return result;
  11: }
  13: public void NullSafeSet(IDbCommand cmd, object value, int index)
  14: {
  15:     if (null != value)
  16:     {
  17:         byte[] buffer = new Guid(((string)value)).ToByteArray();
  18:         NHibernateUtil.Binary.NullSafeSet(cmd, buffer, index);
  19:         Array.Clear(buffer, 0, buffer.Length);
  20:     }
  21: }

The rest part of the class is a normal implementation of a NHibernate custom type.

Now the interesting part is the hbm mapping file. We can use our type in any property even as id, declaring the generator as “assigned” because we can’t use any algorithm to assign the id value.

   1: <?xml version="1.0" encoding="utf-8" ?>
   2: <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
   3:                 assembly="MySolution"
   4:                 namespace="MySolution.Common.Entities">
   5:     <class name="Class1"
   6:             table="TABLE1">
   7:         <id name="Id"
   8:             type="MySolution.Data.Types.RawType, MySolution.Data">
   9:             <column name="ID" />
  10:             <generator class="assigned" />
  11:         </id>
  12:     <!-- ... -->
  13:     </class>
  14: </hibernate-mapping>

The last thing, I implemented an helper static method which generates a new “guid” from scratch.

   1: public static string GenerateNewId()
   2: {
   3:     Guid nextVal = Guid.NewGuid();
   4:     return nextVal.ToString("N");
   5: }

I hope this helps, I spend much time in finding a solution to my problem, because in the net there’s no so much on the argument, so I had to “create” a solution for my project. The next post would be another kind of serializing/deserializing binary raw to and from an Oracle Database taking care when the RAW column is not sure that contains a “well formed” binary data to build a System.Guid.

Here is the complete RawType code:

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Data;
   4: using System.Text;
   5: using NHibernate;
   6: using NHibernate.SqlTypes;
   7: using NHibernate.UserTypes;
   9: namespace MySolution.Data.Types
  10: {
  11:     public class RawType : IUserType
  12:     {
  13:         public static string GenerateNewId()
  14:         {
  15:             Guid nextVal = Guid.NewGuid();
  16:             return nextVal.ToString("N");
  17:         }
  18:         private static SqlType[] types = new SqlType[]
  19:             { new SqlType(DbType.Binary) };
  21:         public object Assemble(object cached, object owner)
  22:         {
  23:             return DeepCopy(cached);
  24:         }
  26:         public new bool Equals(object x, object y)
  27:         {
  28:             return (x == null ? false : x.Equals(y));
  29:         }
  31:         public object DeepCopy(object value)
  32:         {
  33:             return value;
  34:         }
  36:         public object Disassemble(object value)
  37:         {
  38:             return DeepCopy(value);
  39:         }
  41:         public int GetHashCode(object x)
  42:         {
  43:             return x.GetHashCode();
  44:         }
  46:         public bool IsMutable
  47:         {
  48:             get { return true; }
  49:         }
  51:         public object NullSafeGet(IDataReader rs, string[] names, object owner)
  52:         {
  53:             string result = null;
  54:             byte[] buffer = (byte[])NHibernateUtil.Binary.NullSafeGet(rs, names[0]);
  55:             if (null != buffer)
  56:             {
  57:                 result = new Guid(buffer).ToString("N");
  58:                 Array.Clear(buffer, 0, buffer.Length);
  59:             }
  60:             return result;
  61:         }
  63:         public void NullSafeSet(IDbCommand cmd, object value, int index)
  64:         {
  65:             if (null != value)
  66:             {
  67:                 byte[] buffer = new Guid(((string)value)).ToByteArray();
  68:                 NHibernateUtil.Binary.NullSafeSet(cmd, buffer, index);
  69:                 Array.Clear(buffer, 0, buffer.Length);
  70:             }
  71:         }
  73:         public object Replace(object original, object target, object owner)
  74:         {
  75:             return original;
  76:         }
  78:         public Type ReturnedType
  79:         {
  80:             get { return typeof(string); }
  81:         }
  83:         public SqlType[] SqlTypes
  84:         {
  85:             get { return types; }
  86:         }
  87:     }
  88: }


Technorati: ,