Thread: [nhibernate-issues] [NHJIRA] Created: (NH-2713) When calling Oracle stored procedure with Custom Ty

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 422-6466

When calling Oracle stored procedure with Custom Type as parameter , NHibernate is throwing exception. where as with MS SQL  Server 2008 it is working.
-------------------------------------------------------------------------------------------------------------------------------------------------------

                 Key: NH-2713
                 URL: http://216.121.112.228/browse/NH-2713
             Project:  NHibernate
          Issue Type: Bug
          Components: Core, DataProviders / Dialects
    Affects Versions: 3.0.0.GA
            Reporter: Anupam Mishra
         Attachments: Oracle Exception.txt

Please check the attached Exception while calling Oracle Stored Procedure .

Class which implement IType

using System;
using System.Collections.Generic;
using System.Text;
using NHibernate.Type;
using NHibernate.SqlTypes;
using System.Data;
using NHibernate;
using System.Data.SqlClient;
using Oracle.DataAccess.Client;

namespace DomainObject
{
    public class Sql2008Structured : IType
    {
        private static readonly SqlType[] x = new[] { new SqlType(DbType.Object) };

        public SqlType[] SqlTypes(NHibernate.Engine.IMapping mapping)
        {
            return x;
        }

        public bool IsCollectionType
        {
            get { return true; }
        }

        public int GetColumnSpan(NHibernate.Engine.IMapping mapping)
        {
            return 1;
        }      

        #region IType Members

        public int Compare(object x, object y, EntityMode? entityMode)
        {
            throw new NotImplementedException();
        }

        public object DeepCopy(object val, EntityMode entityMode, NHibernate.Engine.ISessionFactoryImplementor factory)
        {
            throw new NotImplementedException();
        }

        public object FromXMLNode(System.Xml.XmlNode xml, NHibernate.Engine.IMapping factory)
        {
            throw new NotImplementedException();
        }

        public int GetHashCode(object x, EntityMode entityMode, NHibernate.Engine.ISessionFactoryImplementor factory)
        {
            throw new NotImplementedException();
        }

        public int GetHashCode(object x, EntityMode entityMode)
        {
            throw new NotImplementedException();
        }

        public IType GetSemiResolvedType(NHibernate.Engine.ISessionFactoryImplementor factory)
        {
            throw new NotImplementedException();
        }

        public object Hydrate(IDataReader rs, string[] names, NHibernate.Engine.ISessionImplementor session, object owner)
        {
            throw new NotImplementedException();
        }

        public bool IsAnyType
        {
            get { throw new NotImplementedException(); }
        }

        public bool IsAssociationType
        {
            get { throw new NotImplementedException(); }
        }

        public bool IsComponentType
        {
            get { throw new NotImplementedException(); }
        }

        public bool IsDirty(object old, object current, bool[] checkable, NHibernate.Engine.ISessionImplementor session)
        {
            throw new NotImplementedException();
        }

        public bool IsDirty(object old, object current, NHibernate.Engine.ISessionImplementor session)
        {
            throw new NotImplementedException();
        }

        public bool IsEntityType
        {
            get { throw new NotImplementedException(); }
        }

        public bool IsEqual(object x, object y, EntityMode entityMode, NHibernate.Engine.ISessionFactoryImplementor factory)
        {
            throw new NotImplementedException();
        }

        public bool IsEqual(object x, object y, EntityMode entityMode)
        {
            throw new NotImplementedException();
        }

        public bool IsModified(object oldHydratedState, object currentState, bool[] checkable, NHibernate.Engine.ISessionImplementor session)
        {
            throw new NotImplementedException();
        }

        public bool IsMutable
        {
            get { throw new NotImplementedException(); }
        }

        public bool IsSame(object x, object y, EntityMode entityMode)
        {
            throw new NotImplementedException();
        }

        public bool IsXMLElement
        {
            get { throw new NotImplementedException(); }
        }

        public string Name
        {
            get { throw new NotImplementedException(); }
        }

        public object NullSafeGet(IDataReader rs, string name, NHibernate.Engine.ISessionImplementor session, object owner)
        {
            throw new NotImplementedException();
        }

        public object NullSafeGet(IDataReader rs, string[] names, NHibernate.Engine.ISessionImplementor session, object owner)
        {
            throw new NotImplementedException();
        }
        public void NullSafeSet(IDbCommand st, object value, int index, bool[] settable, NHibernate.Engine.ISessionImplementor session)
        {
            var s = st as SqlCommand;
            if (s != null)
            {
                s.Parameters[index].SqlDbType = SqlDbType.Structured;
                s.Parameters[index].TypeName = "EmployeeType";
                s.Parameters[index].Value = value;
            }
            else
            {
                var o = st as OracleCommand;
                o.Parameters[index].OracleDbType = OracleDbType.Array;
                o.Parameters[index].UdtTypeName = "TESTTYPE";
                o.Parameters[index].Value = value;
            }
        }      

        public object Replace(object original, object target, NHibernate.Engine.ISessionImplementor session, object owner, System.Collections.IDictionary copyCache, ForeignKeyDirection foreignKeyDirection)
        {
            throw new NotImplementedException();
        }

        public object Replace(object original, object target, NHibernate.Engine.ISessionImplementor session, object owner, System.Collections.IDictionary copiedAlready)
        {
            throw new NotImplementedException();
        }

        public object ResolveIdentifier(object value, NHibernate.Engine.ISessionImplementor session, object owner)
        {
            throw new NotImplementedException();
        }

        public Type ReturnedClass
        {
            get { throw new NotImplementedException(); }
        }

        public object SemiResolve(object value, NHibernate.Engine.ISessionImplementor session, object owner)
        {
            throw new NotImplementedException();
        }

        public void SetToXMLNode(System.Xml.XmlNode node, object value, NHibernate.Engine.ISessionFactoryImplementor factory)
        {
            throw new NotImplementedException();
        }

        public bool[] ToColumnNullness(object value, NHibernate.Engine.IMapping mapping)
        {
            throw new NotImplementedException();
        }

        public string ToLoggableString(object value, NHibernate.Engine.ISessionFactoryImplementor factory)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region ICacheAssembler Members

        public object Assemble(object cached, NHibernate.Engine.ISessionImplementor session, object owner)
        {
            throw new NotImplementedException();
        }

        public void BeforeAssemble(object cached, NHibernate.Engine.ISessionImplementor session)
        {
            throw new NotImplementedException();
        }

        public object Disassemble(object value, NHibernate.Engine.ISessionImplementor session, object owner)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region IType Members

        public void NullSafeSet(IDbCommand st, object value, int index, NHibernate.Engine.ISessionImplementor session)
        {
            var s = st as SqlCommand;
            if (s != null)
            {
                s.Parameters[index].SqlDbType = SqlDbType.Structured;
                s.Parameters[index].TypeName = "EmployeeType";
                s.Parameters[index].Value = value;
            }
            else
            {
                var o = st as OracleCommand;
                o.Parameters[index].OracleDbType = OracleDbType.Array;
                o.Parameters[index].UdtTypeName = "TESTTYPE";
                o.Parameters[index].Value = value;
            }  
        }      

        #endregion
    }
    public static class StructuredExtensions
    {
        private static readonly Sql2008Structured structured = new Sql2008Structured();
        public static IQuery SetStructured(this IQuery query, string name, DataTable dt)
        {
            return query.SetParameter(name, dt, structured);
        }
        public static IQuery SetOracleStructured(this IQuery query, string name, Object[] dt)
        {
            return query.SetParameter(name, dt, structured);
        }
    } 
}

Type defined in Oracle

create or replace
TYPE employeeType AS OBJECT (employeeId INT, employeeName VARCHAR2 (50)); 
***************
create or replace
TYPE TESTTYPE AS TABLE OF EMPLOYEETYPE;

Stored Procedure

create or replace PROCEDURE TESTCUSTOMEMPLOYEE (PARAM1 OUT SYS_REFCURSOR,PARAM IN TESTTYPE) IS
BEGIN
 open PARAM1 for SELECT EMP_ID,EMP_NAME,EMP_PASSWORD,TEAM_ASSOCIATED_WITH,IS_CAPTAIN,NO_OF_MOM,BALANCE  FROM employee; 
END;

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://216.121.112.228/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira