NHibernate - Oracle 11g Configuration for XmlType

Question

Im trying to get NHibernate to work with Oracle 11g´s XmlType.

The following Exception is thrown:

Dialect does not support DbType.Xml

My configuration looks like:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="dialect">NHibernate.Dialect.Oracle10gDialect</property>
    <property name="connection.driver_class">NHibernate.Driver.OracleClientDriver</property>
    <property name="connection.connection_string">...</property>
    <property name="show_sql">false</property>
  </session-factory>
</hibernate-configuration>

Sure, the XmlType functionality was introduced in 11g but I dont know the configuration Mapping. Anyone here using this feature and willing to show its config?

Thanks.

Edit 1:

The important part of the class:

    [DataMember]
    public virtual XmlDocument History { get; set; }

The important part of the mapping file:

    <property name="History" />

SHSE · Accepted Answer · 2012-09-21 16:33:42Z

Try to use custom Oracle data client driver, here is the code:

using System;
using System.Data;
using System.Data.Common;
using System.Reflection;
using NHibernate;
using NHibernate.AdoNet;
using NHibernate.Driver;
using NHibernate.Engine.Query;
using NHibernate.SqlTypes;
using NHibernate.Util;
using Oracle.DataAccess.Client;

/// <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 CustomOracleDataClientDriver : 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 CustomOracleDataClientDriver()
        : base(
            DriverAssemblyName,
            ConnectionTypeName,
            CommandTypeName) {
        var parameterType = ReflectHelper.TypeFromAssembly("Oracle.DataAccess.Client.OracleParameter",
            DriverAssemblyName, false);
        this.oracleDbType = parameterType.GetProperty("OracleDbType");

        var oracleDbTypeEnum = ReflectHelper.TypeFromAssembly("Oracle.DataAccess.Client.OracleDbType",
            DriverAssemblyName, false);
        this.oracleDbTypeRefCursor = Enum.Parse(oracleDbTypeEnum, "RefCursor");
        this.oracleDbTypeXmlType = 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 ":"; }
    }

    #region IEmbeddedBatcherFactoryProvider Members

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

    #endregion

    /// <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 = this.FormatNameForParameter(name);
                this.oracleDbType.SetValue(dbParam, this.oracleDbTypeXmlType, null);
                break;
            default:
                base.InitializeParameter(dbParam, name, sqlType);
                break;
        }
    }

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

        ((OracleCommand) command).BindByName = true;

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

        if (!detail.IsCallable)
            return;

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

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

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

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

NHibernate configuration:

...
<property name="connection.driver_class">
  Your.NameSpace.CustomOracleDataClientDriver, Your.Assembly.Name
</property>
<property name="dialect">
  NHibernate.Dialect.Oracle10gDialect
</property>
...

Here is an example https://gist.github.com/3762475

In which Asseblies, do I find the ExecuteType or IDbProfiler classes?
Well, you can try to specify sql-type in the mapping (see the gist), I have just tested, it works for me (.NET 4.0, Oracle.DataAccess v4.112.3.0), here is the full source code gist.github.com/3762475
I did exactly the sam as u did and I am now getting the Exception: unknown identifier. I think thats because my Oracle.DataAccess is in version: 2.112.1.0 ... but I would the one for .Net 4.0 ...
Well it finally worked. I was still using the old DataAccess Version, my problem was awrong named Column (Date = reserved!)

Collectives™ on Stack Overflow

NHibernate - Oracle 11g Configuration for XmlType

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related