Can not save empty string in Oracle

Serega · **Posted:** Fri Jun 17, 2005 7:34 am

When I try to save object with empty string field, appears this exception,
but when string value is null, it works correctly.

Quote:

Unhandled Exception: NHibernate.ADOException: could not synchronize database state with session ---> System.Exception: Parameter ':p19': No size set for variable length data type: String.
at System.Data.OracleClient.OracleParameterBinding.PrepareForBind(OracleConnection connection, Int32& offset)
at System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, CommandBehavior behavior, Boolean isReader, Boolean needRowid, OciHandle& rowidDescriptor, ArrayList& refCursorParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciHandle& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciHandle& rowidDescriptor)
at System.Data.OracleClient.OracleCommand.ExecuteNonQuery()
at NHibernate.Impl.BatcherImpl.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.Impl.NonBatchingBatcher.AddToBatch(Int32 expectedRowCount)
at NHibernate.Persister.EntityPersister.Update(Object id, Object[] fields, Boolean[] includeProperty, Object oldVersion, Object obj, SqlString sqlUpdateString, ISessionImplementor session)
at NHibernate.Persister.EntityPersister.Update(Object id, Object[] fields, Int32[] dirtyFields, Object[] oldFields, Object oldVersion, Object obj, ISessionImplementor session)
at NHibernate.Impl.ScheduledUpdate.Execute()
at NHibernate.Impl.SessionImpl.ExecuteAll(IList list)
at NHibernate.Impl.SessionImpl.Execute()
--- End of inner exception stack trace ---
at NHibernate.Impl.SessionImpl.Execute()
at NHibernate.Impl.SessionImpl.Flush()

Thanks for your help,
Serega

KPixel · **Posted:** Sat Jun 18, 2005 11:26 am

Can you post your mapping files (hbm.xml) ?

And look at the generated SQL to know which parameter is ':p19'.

sergey · **Posted:** Sat Jun 18, 2005 11:43 am

This is a known bug because Oracle does some strange things with null/empty strings. We don't have Oracle installed (yet), so we can't fix it, but I will integrate a fix if one is proposed.

nokiola · **Joined:** Sun Jun 19, 2005 2:21 pm **Posts:** 21

It is a bug in OracleClient provider and occurs only when inserting/updating an empty string in a varchar column.

Since I have the same problem as you, I would like to propose NHibernate developers to integrate a workaround into NHibernate source to check:

If all these conditions evaluate to true, update parameter value before sending to Oracle provider with DBNull.Value.

Serega · **Posted:** Wed Jul 06, 2005 10:08 am

This problem is more annoying than it may seem, because I can not always check fields manually before saving/updating and it cause exceptions.

mklaus · **Posted:** Wed Jul 06, 2005 6:37 pm

I believe there's already an issue open for this: http://jira.nhibernate.org/browse/NH-162

The fix posted in this issue sets the size of the parameter string which makes Oracle happy.

Mike

Serega · **Posted:** Fri Jul 08, 2005 10:55 am

Thanks! It really works but after small correction

rponton · **Posted:** Wed Jul 13, 2005 4:45 pm

I used an Interceptor.

Code:

      public static bool EmptyStringToNull(object[] state, NHibernate.Type.IType[] types)
      {
         bool modified = false;
         for (int i = 0; i < types.Length; i++)
         {
            if ( (types[i] is NHibernate.Type.AnsiStringType || state[i] is string) 
               && state[i] != null
               && state[i].ToString() == "")
            {
               state[i] = null;
               modified = true;
            }
         }
         return modified;
      }

Code:

      public bool OnFlushDirty(object entity, object id, object[] currentState, object[] previousState, string[] propertyNames, NHibernate.Type.IType[] types)
      {
         bool modified = EmptyStringToNull(currentState, types);
         return modified;
      }

      public bool OnSave(object entity, object id, object[] state, string[] propertyNames, NHibernate.Type.IType[] types)
      {
         bool modified = EmptyStringToNull(state, types);
         return modified;
      }

I made this to work with Oracle8i specifically, so you may want to adjust the IF statement to test for more than AnsiString.