This discussion is archived
1 Reply Latest reply: Dec 20, 2012 11:55 AM by willjamu RSS

Transaction Scope and Oracle and SQL Server

willjamu Newbie
Currently Being Moderated
I was thinking the below fix post 11.2.0.3 was the solution to my issue.

Oracle 11.2.0.3 client with the be patch against an Oracle 11.2.0.2 database and a SQL 2012 instance.

*12352406 - PROBLEM IN COMMITTING A TRANSACTION IS MSDTC WHEN SQL CONNECTION IS OPENED FIRST*

Basically, I have cobbled togther some code from different sources to perform a POC using MSDTC to backout transactions from two different resources managers (Oracle and SQL Server)

In the below code if SQL is first it works as expected and if Oracle is first it works as expected.


However, I can't get both resource managers to properly participate in a transaction.


using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using Oracle.DataAccess.Client;

using Oracle.DataAccess.Types;

using System.Data.SqlClient;

using System.Transactions;



namespace tran_sproc3

{

class Program

{

static void Main(string[] args)

{



string _connstring = "Data Source=TNS1,User Id=scott;Password=tiger;";

try

{

OracleConnection connObj = new OracleConnection(connstring);

// OracleTransaction _tranObj;

//_connObj.Open();

//_tranObj = _connObj.BeginTransaction();

OracleCommand cmdObj = connObj.CreateCommand();





SqlConnection cn = new SqlConnection("server=(local)\\SQL2012;database=DB1;Integrated Security=SSPI;");

SqlCommand cmd = cn.CreateCommand();



try

{



//using (TransactionScope scope = new TransactionScope())

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))

{



//* SQL Server

cn.Open();

cmd.CommandText = "pTest";

cmd.CommandType = CommandType.StoredProcedure;

cmd.ExecuteNonQuery();

Console.WriteLine("SQL Server Stored proc run successfully");



//* oracle

_connObj.Open();

cmdObj.CommandText = "procInsertSamplePODetails";

_cmdObj.CommandType = CommandType.StoredProcedure;

_cmdObj.Parameters.Clear();

_cmdObj.ExecuteNonQuery();

Console.WriteLine("Oracle Stored proc run successfully");



//Intentionally cause an exception

/*

_cmdObj.CommandText = "INSERT INTO NonExistentTable(InvID, InvDate, Remarks) VALUES(:InvID, SYSDATE, :Remarks)";

_cmdObj.CommandType = CommandType.Text;

_cmdObj.Parameters.Clear();

_cmdObj.Parameters.Add(new OracleParameter("InvID", "A02"));

_cmdObj.Parameters.Add(new OracleParameter("Remarks", "Sample invoice 2"));

_cmdObj.ExecuteNonQuery();

//_tranObj.Commit(); */



scope.Complete();



}



}

catch (Exception ex)

{

Console.WriteLine("Uh oh, rollback initiated...");

Console.WriteLine(ex.ToString());

// _tranObj.Rollback();

// }



}

finally

{

//_tranObj.Commit();

_connObj.Close();

_connObj.Dispose();

_connObj = null;

cn.Close();

cn.Dispose();

cn = null;

Console.WriteLine();

Console.WriteLine("ENTER to continue...");

Console.ReadLine();

}



}

catch (Exception ex)

{

Console.WriteLine(ex.ToString());

}

}

}

}
  • 1. Re: Transaction Scope and Oracle and SQL Server
    willjamu Newbie
    Currently Being Moderated
    Follow these steps only if you are using ODP.NET:
    1.
    Open a command prompt. (Open via "Run As Administrator" if applying on Windows 7 or 2008R2 and User Account Control is enabled).

    2.
    Navigate to the %ORACLE_HOME%\ODP.NET\bin\2.x directory.

    3.
    Execute the following to install the new ODP.NET assembly:
    oraprovcfg.exe /action:gac /providerpath:Oracle.DataAccess.dll
    Basically I neglected to reload the GAC after installing the patch.

    The below code now works as expected


    3.3.6 ODP.NET 4.0 Installation Instructions

    Follow these steps only if you are using ODP.NET:
    1.
    Open a command prompt. (Open via "Run As Administrator" if applying on Windows 7 or 2008R2 and User Account Control is enabled).

    2.
    Navigate to the %ORACLE_HOME%\ODP.NET\bin\4 directory.

    3.
    Execute the following to install the new ODP.NET assembly:
    oraprovcfg.exe /action:gac /providerpath:Oracle.DataAccess.dll

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points