2 Replies Latest reply on Nov 7, 2013 5:21 PM by Alex Keh - Product Manager-Oracle

    Bug: Oracle Managed Provider and comments in SQL

      Hi, I'm testing the Managed provider and NHibernate and I found that the Managed Provider has problems with comments in sql.

      I have AutoCommentSql option enabled in NHibernate configuration and it adds a comment at the beginning of sql like /* [expression] */ select
      With comment the oracle command does not return any rows, when I remove the comment I get the result.

      For now the workaround is to set AutoCommentSql to false.

      Here it is my Oracle Managed Driver:
      public class OracleManagedDataClientDriver : ReflectionBasedDriver, IEmbeddedBatcherFactoryProvider
      private const string driverAssemblyName = "Oracle.ManagedDataAccess";
      private const string connectionTypeName = "Oracle.ManagedDataAccess.Client.OracleConnection";
      private const string commandTypeName = "Oracle.ManagedDataAccess.Client.OracleCommand";
      private static readonly SqlType GuidSqlType = new SqlType(DbType.Binary, 16);
      private readonly PropertyInfo oracleCommandBindByName;
      private readonly PropertyInfo oracleDbType;
      private readonly object oracleDbTypeRefCursor;

      /// <summary>
      /// Initializes a new instance of <see cref="OracleManagedDataClientDriver"/>.
      /// </summary>
      /// <exception cref="HibernateException">
      /// Thrown when the <c>Oracle.ManagedDataAccess</c> assembly can not be loaded.
      /// </exception>
      public OracleManagedDataClientDriver()
      : base(
      System.Type oracleCommandType = ReflectHelper.TypeFromAssembly("Oracle.ManagedDataAccess.Client.OracleCommand", driverAssemblyName, false);
      oracleCommandBindByName = oracleCommandType.GetProperty("BindByName");

      System.Type parameterType = ReflectHelper.TypeFromAssembly("Oracle.ManagedDataAccess.Client.OracleParameter", driverAssemblyName, false);
      oracleDbType = parameterType.GetProperty("OracleDbType");

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

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

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

      /// <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);
      case DbType.Guid:
      base.InitializeParameter(dbParam, name, GuidSqlType);
      base.InitializeParameter(dbParam, name, sqlType);

      protected override void OnBeforePrepare(IDbCommand command)

      // need to explicitly turn on named parameter binding
      // http://tgaw.wordpress.com/2006/03/03/ora-01722-with-odp-and-command-parameters/
      oracleCommandBindByName.SetValue(command, true, null);

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

      if (!detail.IsCallable)

      command.CommandType = CommandType.StoredProcedure;
      command.CommandText = detail.FunctionName;
      oracleCommandBindByName.SetValue(command, false, null);

      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); }


      Edited by: Invincible on Oct 2, 2012 7:50 AM