NHibernate Oracle XMLType issue

We have one function in oracle 10g database which returns a XMLTYPE value. we are trying to get the returned value from function using ParameterDirection.ReturnValue in command object.

but we are getting error as

System.Data.OracleClient.OracleException (0x80131938): ORA-06550: line 1, column 27: PLS-00382: expression is of wrong type ORA-06550: line 1, column 7: PL/SQL: Statement ignored

How to resolve this?

It's been a bit since I worked on this and currently I do not have a working project to validate the issue, however, I beleive this will solve you problem.

How I solved this was to do a custom build of the nHibernate project. I edited the OracleDataClientDriver.cs file with the edits below. You should note that I just downloaded the current version of this file and it has change slightly in the last 2 years since I made my fix. If you get the current version of the file from the nHibernate repo and dif the file with the code I am going to paste, you will see the changes. My changes are on line numbers (as per my editor) 26, 45, 82-85.

The theme of the fix is that I had to add a mapping from the nHibernate mapping file types to the Oracle Data Type for XML. The Oracle driver will handle this type, but the logic to use it in nHibernate was missing. Below is the fix for this.

Start Paste-----

using System.Data;
using System.Reflection;
using NHibernate.AdoNet;
using NHibernate.Engine.Query;
using NHibernate.SqlTypes;
using NHibernate.Util;

namespace NHibernate.Driver
{
    /// <summary>
    /// A NHibernate Driver for using the Oracle.DataAccess DataProvider
    /// </summary>
    /// <remarks>
    /// Code was contributed by <a href="http://sourceforge.net/users/jemcalgary/">James Mills</a>
    /// on the NHibernate forums in this 
    /// <a href="http://sourceforge.net/forum/message.php?msg_id=2952662">post</a>.
    /// </remarks>
    public class OracleDataClientDriver : ReflectionBasedDriver, IEmbeddedBatcherFactoryProvider
    {
        private const string driverAssemblyName = "Oracle.DataAccess";
        private const string connectionTypeName = "Oracle.DataAccess.Client.OracleConnection";
        private const string commandTypeName = "Oracle.DataAccess.Client.OracleCommand";
        private static readonly SqlType GuidSqlType = new SqlType(DbType.Binary, 16);
        private readonly PropertyInfo oracleDbType;
        private readonly object oracleDbTypeRefCursor; 
        private readonly object oracleDbTypeXmlType;

        /// <summary>
        /// Initializes a new instance of <see cref="OracleDataClientDriver"/>.
        /// </summary>
        /// <exception cref="HibernateException">
        /// Thrown when the <c>Oracle.DataAccess</c> assembly can not be loaded.
        /// </exception>
        public OracleDataClientDriver()
            : base(
            driverAssemblyName,
            connectionTypeName,
            commandTypeName)
        {
            System.Type parameterType = ReflectHelper.TypeFromAssembly("Oracle.DataAccess.Client.OracleParameter", driverAssemblyName, false);
            oracleDbType = parameterType.GetProperty("OracleDbType");

            System.Type oracleDbTypeEnum = ReflectHelper.TypeFromAssembly("Oracle.DataAccess.Client.OracleDbType", driverAssemblyName, false);
            oracleDbTypeRefCursor = System.Enum.Parse(oracleDbTypeEnum, "RefCursor");
            oracleDbTypeXmlType = System.Enum.Parse(oracleDbTypeEnum, "XmlType");
        }

        /// <summary></summary>
        public override bool UseNamedPrefixInSql
        {
            get { return true; }
        }

        /// <summary></summary>
        public override bool UseNamedPrefixInParameter
        {
            get { return true; }
        }

        /// <summary></summary>
        public override string NamedPrefix
        {
            get { return ":"; }
        }

        /// <remarks>
        /// This adds logic to ensure that a DbType.Boolean parameter is not created since
        /// ODP.NET doesn't support it.
        /// </remarks>
        protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType)
        {
            // if the parameter coming in contains a boolean then we need to convert it 
            // to another type since ODP.NET doesn't support DbType.Boolean
            switch (sqlType.DbType)
            {
                case DbType.Boolean:
                    base.InitializeParameter(dbParam, name, SqlTypeFactory.Int16);
                    break;
                case DbType.Guid:
                    base.InitializeParameter(dbParam, name, GuidSqlType);
                    break;
                case DbType.Xml:
                    dbParam.ParameterName = base.FormatNameForParameter(name);
                    oracleDbType.SetValue(dbParam, oracleDbTypeXmlType, null);
                    break;
                default:
                    base.InitializeParameter(dbParam, name, sqlType);
                    break;
            }
        }

        protected override void OnBeforePrepare(IDbCommand command)
        {
            base.OnBeforePrepare(command);

            CallableParser.Detail detail = CallableParser.Parse(command.CommandText);

            if (!detail.IsCallable)
                return;

            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = detail.FunctionName;

            IDbDataParameter outCursor = command.CreateParameter();
            oracleDbType.SetValue(outCursor, oracleDbTypeRefCursor, null);

            outCursor.Direction = detail.HasReturn ? ParameterDirection.ReturnValue : ParameterDirection.Output;

            command.Parameters.Insert(0, outCursor);
        }

        #region IEmbeddedBatcherFactoryProvider Members

        System.Type IEmbeddedBatcherFactoryProvider.BatcherFactoryClass
        {
            get { return typeof (OracleDataClientBatchingBatcherFactory); }
        }

        #endregion
    }

End Paste-------