Using TransactionScope with System.Data.OracleClient - TransactionAbortedException

Question

My system write some data to a SQL Server DB (2008), extracts it later and processes it some more, before writing it to an Oracle (10g) DB.

I've wrapped my SQL Server interactions in a TransactionScope but when I try the same think with my Oracle interactions I get a `TranactionAbortedException - "The transaction has aborted".

Remove the TransactionScope, and everything works OK.

I could always revert back to manually managing my own transactions, but I'm hoping there is a simple solution.

Sample code:

private static void OracleTest()
    {           
        using (TransactionScope ts = new TransactionScope())
        {
            using (OracleConnection conn = new OracleConnection(connString))
            {
                try
                {
                    using (OracleCommand cmd = new OracleCommand())
                    {
                        cmd.CommandText = "MyPackage.MyFunction";
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.Connection = conn;

                        OracleParameter param = cmd.Parameters.Add(new OracleParameter("field1_", "abc123"));

                        param = cmd.Parameters.Add(new OracleParameter("rs_", OracleType.Cursor));
                        param.Direction = System.Data.ParameterDirection.Output;
                        conn.Open();
                        using (OracleDataReader dr = cmd.ExecuteReader())
                        {

I haven't found anything to suggest that you can't use TransactionScopes with Oracle, but as you can see from my example, we're falling at the first hurdle (when we open the connection) so it's hard to see where I might be going wrong.

Answer 1

You may want to take a look at this; I haven't had much luck with TransactionScope in Oracle 10g either: http://forums.oracle.com/forums/thread.jspa?messageID=4127323