none
unable to get transactions to work with OracleClient RRS feed

  • Question

  • I am using VS2008, System.Data.OracleClient, Oracle 10g, and ODAC 10.2.0.20.  I haven't been able to get transactions to work.  When I use 'connection.BeginTransaction()', the rollback doesn't work.  When I use TransactionScope, the output parameter is always DBNull.  Any ideas/comments?

    Here's the sample code:

    // #define ENABLE_TRANSACTION     // failure is 'rollback not working'
    #define ENABLE_TRANSACTION_SCOPE  // failure is 'no output parameter value'
    
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Text;
    using System.Data.OracleClient;
    #if ENABLE_TRANSACTION_SCOPE
    using System.Transactions;
    #endif
    
    namespace TestOracleTransaction
    {
      class Program
      {
        static void Main(string[] args)
        {
    #if ENABLE_TRANSACTION_SCOPE
          using (TransactionScope scope = new TransactionScope())
    #endif
          {
            string connectionString = "Data Source=ORADEV;User ID=user;Password=pwd";
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
              try
              {
                connection.Open();
    
    #if ENABLE_TRANSACTION
                using (OracleTransaction transaction = connection.BeginTransaction())
    #endif
                {
                  try
                  {
    #if ENABLE_TRANSACTION_SCOPE
                    if (Transaction.Current == null)
                    {
                      throw new ArgumentException("no ambient transaction found for OracleClient");
                    }
    #endif
    
                    OracleCommand command = connection.CreateCommand();
    #if ENABLE_TRANSACTION
                    command.Transaction = transaction;
    #endif
    
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "TIS.P_TIS_GATEWAY_INFO_ADD";
    
                    OracleParameter param = command.CreateParameter();
                    param.ParameterName = "p_gateway_id";
                    param.Direction = ParameterDirection.Input;
                    param.DbType = DbType.Int64;
                    param.Value = 18;
                    command.Parameters.Add(param);
    
                    param = command.CreateParameter();
                    param.ParameterName = "p_info_id";
                    param.Direction = ParameterDirection.Input;
                    param.DbType = DbType.Int64;
                    param.Value = 79;
                    command.Parameters.Add(param);
    
                    param = command.CreateParameter();
                    param.ParameterName = "p_user";
                    param.Direction = ParameterDirection.Input;
                    param.DbType = DbType.String;
                    param.Value = "spms";
                    command.Parameters.Add(param);
    
                    param = command.CreateParameter();
                    param.ParameterName = "p_gateway_info_id";
                    param.Direction = ParameterDirection.Output;
                    param.DbType = DbType.Int64;
                    param.Size = sizeof(Int64);
                    command.Parameters.Add(param);
    
                    int count = command.ExecuteNonQuery();
    
                    object value = command.Parameters["p_gateway_info_id"].Value;
    
                    long id = (value == DBNull.Value) ? -1 : Convert.ToInt64(value);
                    if (id < 0)
                    {
                      // FAILURE - no output parameter value when TransactionScope enabled
                      throw new ArgumentException("no return value");
                    }
    
    #if ENABLE_TRANSACTION
                    // FAILURE - rollback doesn't work when Transaction enabled
                    transaction.Rollback();
    #endif
    #if ENABLE_TRANSACTION_SCOPE
                    scope.Complete();
    #endif
                  }
                  catch (Exception ex)
                  {
                    System.Console.WriteLine("ERROR: " + ex.Message);
    #if ENABLE_TRANSACTION
                    transaction.Rollback();
    #endif
                  }
                }
              }
              finally
              {
                if (connection.State == ConnectionState.Open)
                {
                  connection.Close();
                }
              }
            }
          }
        }
      }
    }
    

     

    Thursday, April 22, 2010 11:39 PM

Answers

  • Since you suggested it, I posted this message to an Oracle forum and someone created a stored proc and tried to recreate the problem.  Since they weren't able to reproduce these 2 failures, I decided to take a look at the stored proc and found a COMMIT statement.  When I removed the COMMIT statement, everything started working.  Not sure if there's a better solution but at least I can moved forward again.  Problem solved!  8^)

    Thanks,
    Jerry

    Friday, April 23, 2010 3:26 PM

All replies

  • Hello Jerry,

     

    Welcome to ADO.NET Managed Providers forum!

     

    Basically the forum here is for ADO.NET managed providers especially System.Data.SqlClient, System.Data.Odbc and System.Data.Oledb.   For ODAC issues on Oracle database, I recommend you consult the corresponding support channel of Oracle, like http://forums.oracle.com/forums/category.jspa?categoryID=44.   

     

    Hope you have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, April 23, 2010 2:26 AM
    Moderator
  • I read in an ADO.NET blog that System.Data.OracleClient was 'deprecated' in .NET 4.0.  I didn't realize that MS doesn't support it anymore.

    Thanks,
    Jerry

    Friday, April 23, 2010 3:25 AM
  • Hello Jerry,

     

     

    You mentioned you are using ODAC, that's why I redirect you to Oracle forum for better support.   For System.Data.OracleClient, if it is not Microsoft driver that is used, it would be out of the support scope in this forum queue.    Thanks for your understanding. 

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, April 23, 2010 4:30 AM
    Moderator
  • The sample code only uses System.Data.OracleClient.  When both types of transactions are disabled, the OracleClient works perfectly.  When I enable either transaction type, a failure with the OracleClient occurs.  I mentioned ODAC because some other forums have suggested TransactionScope issues related to MSDTC and oramts.dll.  This sample doesn't appear to explicitly use ODAC at all.

    Thanks,
    Jerry

    Friday, April 23, 2010 4:55 AM
  • Hi Jerry,

     

    I can understand the problem.   But I am not 100% sure whether OracleClient is still supported.  I will consult the product team for this.  However, I saw many product team members suggested the customers to change their driver to ODP.NET since OracleClient is “deprecated” as you said.    

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, April 23, 2010 5:16 AM
    Moderator
  • Thanks for checking with the team.  Changing drivers is not an option at this point because I'm using Ent Lib 3.1 and Repository Factory which uses the OracleClient driver.  This sample code boils it down to keep it simple and concise.  Hopefully, deprecated (in .NET 4.0) doesn't mean unsupported yet (in my .NET 3.5 app).

    Thanks,
    Jerry

    Friday, April 23, 2010 5:34 AM
  • It is supported through version 4.0 of the .NET Framework:

    http://blogs.msdn.com/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, April 23, 2010 1:29 PM
  • Since you suggested it, I posted this message to an Oracle forum and someone created a stored proc and tried to recreate the problem.  Since they weren't able to reproduce these 2 failures, I decided to take a look at the stored proc and found a COMMIT statement.  When I removed the COMMIT statement, everything started working.  Not sure if there's a better solution but at least I can moved forward again.  Problem solved!  8^)

    Thanks,
    Jerry

    Friday, April 23, 2010 3:26 PM