14 Replies Latest reply: Jun 8, 2010 4:27 PM by 411565 RSS

    10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?

    542526
      Hello

      10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?

      I read ODP.NET doc it does support .NET TransactionScope

      But what about 10g Express database? My application died silently (i.e. just exit, no exception) on first db call - and if I just comment out TransactionScope, object instance get saved successfully.

      Just found out there isn't an "Express" version for 11g? What should I be using...?

      http://www.oracle.com/technology/software/products/database/index.html

      Thanks

      Edited by: devvvy on Apr 9, 2009 7:48 PM
        • 1. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
          542526
          oh.... tested:
          * ODP.NET version 2.111.6.20
          * Oracle 11g
          * .NET 3.5

          try {
          ...
          using (oScope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.FromSeconds(60)))
                              {
          ...
          DoFirstDatabaseOperation(...); *<< Application just exited!! No exception!* (I also tested if I comment out TransactionScope then everything all good.)
          ...
          } catch(Exception ex)
          {
          // No exception caught!
          }

          I enabled ODP.NET tracing by adding to app.config:
          <oracle.dataaccess.client>
          <settings>
          <add name="TraceFileName" value="c:\odpnet1.trc"/>
          <add name="TraceLevel" value="63"/>
          </settings>
          </oracle.dataaccess.client>

          It looks like we've a *"System.AccessViolationException"* while +"Oracle.DataAccess.Client.ConnectionDispenser.CreateConnectionPool" is invoked. ODP.NET assigned to it the following error code:+
          +     "ODP error code=-3000; ODP message=Data provider internal error"+

          Help anyone? I been haunted by this last few days and looks like it will continue and ruin my Easter holiday.

          ----------------------------------------------------
          Here's the trace:

          TIME:2009/04/10-17:40:18:578 TID: 80c (REGISTRY) (CheckConStatus : 1)
          TIME:2009/04/10-17:40:18:578 TID: 80c (REGISTRY) (DynamicEnlistment : 0)
          TIME:2009/04/10-17:40:18:578 TID: 80c (REGISTRY) (FetchSize : 65536 bytes)
          TIME:2009/04/10-17:40:18:578 TID: 80c (REGISTRY) (PerformanceCounters : 0)
          TIME:2009/04/10-17:40:18:578 TID: 80c (REGISTRY) (PSPE : promotable)
          TIME:2009/04/10-17:40:18:578 TID: 80c (REGISTRY) (StatementCacheSize : 10)
          TIME:2009/04/10-17:40:18:578 TID: 80c (REGISTRY) (StatementCacheWithUdts : 1)
          TIME:2009/04/10-17:40:18:578 TID: 80c (CONFIG) (TraceFileName : c:\odpnet1.trc)
          TIME:2009/04/10-17:40:18:578 TID: 80c (CONFIG) (TraceLevel : 63)
          TIME:2009/04/10-17:40:18:578 TID: 80c (REGISTRY) (TraceOption : 0)
          TIME:2009/04/10-17:40:18:578 TID: 80c (REGISTRY) (UdtCacheSize : 4096 KB)
          TIME:2009/04/10-17:40:18:578 TID: 80c (CONFIG) (MetaDataXml : <none>)
          TIME:2009/04/10-17:40:18:578 TID: 80c (CONFIG) (DbNotificationPort : -1)
          TIME:2009/04/10-17:40:18:578 TID: 80c (REGISTRY) (ThreadPoolMaxSize : -1 Original: 500; Set: -1; Post-Set: 500)
          TIME:2009/04/10-17:40:18:578 TID: 80c (REGISTRY) (DllPath : D:\OracleODPNET\bin)
          TIME:2009/04/10-17:40:18:578 TID: 80c (REGISTRY) (MetadataPooling : 1)
          TIME:2009/04/10-17:40:18:578 TID: 80c (ENTRY) OracleCommand::OracleCommand(1)
          TIME:2009/04/10-17:40:18:578 TID: 80c (EXIT) OracleCommand::OracleCommand(1)
          TIME:2009/04/10-17:40:18:578 TID: 80c (ENTRY) OracleCommand::CommandText(): set
          TIME:2009/04/10-17:40:18:578 TID: 80c (EXIT) OracleCommand::CommandText(): set
          TIME:2009/04/10-17:40:18:609 TID: 80c (ENTRY) OracleConnection::OracleConnection(1)
          TIME:2009/04/10-17:40:18:609 TID: 80c (EXIT) OracleConnection::OracleConnection(1)
          TIME:2009/04/10-17:40:18:625 TID: 80c (ENTRY) OracleConnection::Open()
          TIME:2009/04/10-17:40:18:625 TID: 80c (ENTRY) OpsConAllocValCtx()
          TIME:2009/04/10-17:40:18:625 TID: 80c (EXIT) OpsConAllocValCtx(): RetCode=0 Line=355
          TIME:2009/04/10-17:40:20:656 TID: 80c (ENTRY) OpsConOpen()
          TIME:2009/04/10-17:40:20:671 TID: 80c (ENTRY) OpsConEnlist(): (40d16c8)=0
          TIME:2009/04/10-17:40:20:671 TID: 80c (ENTRY) OpsTraceGetLastErrorCode()
          TIME:2009/04/10-17:40:20:671 TID: 80c (EXIT) OpsTraceGetLastErrorCode(): RetCode=0 Line=1389
          TIME:2009/04/10-17:40:20:671 TID: a44 (ENTRY) OpsTraceCreateMiniDump()
          TIME:2009/04/10-17:40:20:671 TID: a44 (EXIT) OpsTraceCreateMiniDump(): None created: TraceLevel=0 Line=1306
          TIME:2009/04/10-17:40:20:671 TID: 80c (EXCPT) Lvl0: (Type=System.AccessViolationException) (Msg=Attempted to read or write protected memory. This is often an indication that other memory is corrupt.) (Win32Err=203) (Code=c0000005) (Stack= at Oracle.DataAccess.Client.OpsCon.Open(IntPtr& opsConCtx, IntPtr& opsErrCtx, OpoConValCtx* pOpoConValCtx, OpoConRefCtx& pOpoConRefCtx)
          at Oracle.DataAccess.Client.ConnectionDispenser.CreateConnectionPool(OpoConCtx& opoConCtx))
          TIME:2009/04/10-17:40:20:765 TID: 80c (ERROR) ODP error code=-3000; ODP message=Data provider internal error
          TIME:2009/04/10-17:40:20:765 TID: 80c (ENTRY) OpsConRelRef(): (0)=1
          TIME:2009/04/10-17:40:20:765 TID: 80c (WARN) OpsConRelRef(): Null ptr: (0)=1
          TIME:2009/04/10-17:40:20:765 TID: 80c (EXIT) OpsConRelRef(): RetCode=1 Line=2590 (0)=0
          • 2. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
            gdarling - oracle
            Hi,

            What is your connect string? I was recently made aware of a similar issue that occurs if you use a fully qualified TNS Alias with TransactionScope ..

            for example, this causes problems:

            constr = "DATA SOURCE=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=hostname)(Port=1521)))(CONNECT_DATA=(SERVICE_NAME=servicename)(SERVER=Dedicated))); user id="..

            this works:
            constr = "DATA SOURCE=mytnsalias;user id="..

            this works:
            constr = "DATA SOURCE=//hostname:port/servicename;user id="..

            however, if you use a really long tns alias, that has issues as well...
            constr ="data source=abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz;user id="...

            Greg
            • 3. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
              Alex.Keh .Product.Manager-Oracle
              Promotable transactions are supported with ODP.NET 11.1.0.7.10 or higher. On the client, you must also use Oracle Services for MTS 11.1.0.7.10 or higher.

              The first resource manager to participate in the transaction must be an Oracle DB 11g DB. Other resource managers may then join the distributed transaction and be "promoted", including 10g Express Edition.

              More details are described in the documentation:
              http://download.oracle.com/docs/cd/E14454_01/win.111/e14083/featADO20.htm#CJAEDJII
              • 4. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
                542526
                Thank you very much Alex!

                #1. ODP.NET version I used is v2.0.50727 (for 11g) which I downloaded and installed yesterday.

                #2. After reading ODP.NET versionin scheme (http://download.oracle.com/docs/html/E10927_01/InstallVersioningScheme.htm) I got the impression "2.111.6.20" (start with "2", for .NET 2 - hopefully good for .NET 3.5 as well since that's what I'm using) is > than versions that starts with "1" (not sure why "11.1.0.7.10" starts with "11"?)

                I thought Oracle for MTS is included in ODAC/ODP.NET package - http://www.oracle.com/technology/software/tech/dotnet/utilsoft.html
                "Oracle Services for Microsoft Transaction Server 11.1.0.6.0"

                I can't (i'm a Oracle newbie) find it on my machine... but reading Oracle for MTS doc now:

                http://www.oracle.com/technology/tech/windows/ora_mts/index.html
                (Checking if this doc is up to date - first glance it seems to be talking about 9i and 8i!)

                I looked in more detail, this is the version that was included:
                     "Oracle Services for Microsoft Transaction Server 11.1.0.6.0" (And this is version which should be included in my package)

                But from doc:
                Oracle Services for MTS 11.1.0.7.10

                I Googled more, it seems only beta version is available as of today?
                http://www.oracle.com/technology/software/tech/windows/odpnet/index1110710beta.html

                http://www.google.ca/search?hl=en&q=Oracle+Services+for+MTS+11.1.0.7.10+download+&btnG=Search&meta=

                btw, I found "OracleMTSRecoveryService" under Windows Service, but this is not "OracleMTSService".
                • 5. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
                  542526
                  Thanks, this is the string I used:

                  Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=LAPPC2)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=DevDB)));User Id=dev;Password=dev;

                  I'm thinking ... I tried set:
                  PROMOTABLE TRANSACTION=LOCAL;

                  I got a different error (instead of application just exit, disappear and die):     "There was an error promoting the transaction to a distributed transaction."
                  (.NET 3.5 level exception, no error found in ODP trace file this time)

                  This should indicate a LOCAL transaction as supposed to distributed trans. Not sure why I had this error message. I Googled for this - there's nothing unfortunately.
                  • 6. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
                    542526
                    oh "11.1.0.7.10" is ODAC version, I have "11.1.6.0.21" and it is not good enough isn't it?

                    But 11.1.0.7.10 is a BETA, there's no get around except resorting to BETA?

                    http://www.oracle.com/technology/software/tech/windows/odpnet/index1110710beta.html
                    • 7. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
                      Alex.Keh .Product.Manager-Oracle
                      You will need to upgrade to ODP.NET 11.1.0.7.10. That ODAC version will also contain the Oracle Services for MTS version you need. It's only available in beta since Oracle just released it.
                      • 8. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
                        542526
                        Alex Keh wrote:
                        You will need to upgrade to ODP.NET 11.1.0.7.10. That ODAC version will also contain the Oracle Services for MTS version you need. It's only available in beta since Oracle just released it.
                        I installed "ODAC 11g Beta 11.1.0.7.10" BETA downloaded from here:
                        http://www.oracle.com/technology/software/tech/windows/odpnet/index1110710beta.html

                        From Oracle Universal Installer I verified that I've installed,
                        ODP.NET 2.0.11.1.0.7.10 Beta
                        Oracle Services for MTS 11.1.0.7.10

                        From Visual Studio, I added reference to "Oracle.DataAccess" and redirect binding to via to:

                        <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
                        <qualifyAssembly partialName="Oracle.DataAccess" fullName="Oracle.DataAccess, Version=2.111.7.10, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
                        </assemblyBinding>

                        Still got an exception same place (although application this time, running on debugger, did not simply exit but hang) - ODP.NET trace reviewed same exception - see stack trace at the bottom

                        Also from ODP.NET DOC on Local Transaction Support - this should be a local transaction:

                        If the database version is Oracle Database 11g release 1 (11.1) or higher, then the
                        +"Promotable Transaction" setting is ignored. A local transaction gets created for the+
                        first connection opened in the System.Transaction scope. This transaction is
                        automatically promoted to a distributed transaction when a second connection is
                        opened. This functionality provides enhanced performance and scalability.

                        I got a feeling this failure has nothing to do with M$DTC or Oracle Services for DTC

                        Thanks!

                        -------------------------------------------------

                        STACK TRACE from ODP.NET

                        TIME:2009/04/12-06:22:20:796 TID:1354 (EXIT) OpsTraceGetLastErrorCode(): RetCode=0 Line=1454
                        TIME:2009/04/12-06:22:20:796 TID: c48 (ENTRY) OpsTraceCreateMiniDump()
                        TIME:2009/04/12-06:22:20:796 TID: c48 (EXIT) OpsTraceCreateMiniDump(): None created: TraceLevel=0 Line=1371
                        TIME:2009/04/12-06:22:20:796 TID:1354 (EXCPT) Lvl0: (Type=System.AccessViolationException) (Msg=Attempted to read or write protected memory. This is often an indication that other memory is corrupt.) (Win32Err=203) (Code=c0000005) (Stack= at Oracle.DataAccess.Client.OpsCon.Enlist(IntPtr opsConCtx, OpoConValCtx* pOpoConValCtx, OpoConRefCtx pOpoConRefCtx)
                        at Oracle.DataAccess.Client.ConnectionDispenser.Enlist(OpoConCtx opoConCtx))
                        TIME:2009/04/12-06:22:20:796 TID:1354 (ENTRY) OpsConClose(): (bbea408)=197043208
                        TIME:2009/04/12-06:22:20:796 TID:1354 (ENTRY) OpsConRelRef(): (bbea408)=197043208
                        TIME:2009/04/12-06:22:20:796 TID:1354 (ENTRY) OpsErrFreeCtx()
                        TIME:2009/04/12-06:22:20:796 TID:1354 (EXIT) OpsErrFreeCtx(): RetCode=0 Line=224
                        TIME:2009/04/12-06:22:20:796 TID:1354 (EXIT) OpsConRelRef(): RefCount=0 RetCode=0 Line=3289 (0)=0
                        TIME:2009/04/12-06:22:20:796 TID:1354 (EXIT) OpsConClose(): RetCode=0 Line=1242 (bbea408)=197043208
                        TIME:2009/04/12-06:22:20:796 TID:1354 (ENTRY) OpsConDispose(): (0)=0
                        TIME:2009/04/12-06:22:20:796 TID:1354 (EXIT) OpsConDispose(): RetCode=0 Line=1320
                        TIME:2009/04/12-06:22:20:812 TID:1354 (ENTRY) OpsConRelRef(): (0)=0
                        TIME:2009/04/12-06:22:20:812 TID:1354 (WARN) OpsConRelRef(): Null ptr: (0)=0
                        TIME:2009/04/12-06:22:20:812 TID:1354 (EXIT) OpsConRelRef(): RetCode=1 Line=3158 (0)=0
                        • 9. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
                          542526
                          I gave up - I used good old IDbTransaction instead of TransactionScope just for Oracle (our app supports different database)

                          IDbTransaction oTx = oConn.BeginTransaction();
                          oTx.Commit();

                          heart broken
                          • 10. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
                            Alex.Keh .Product.Manager-Oracle
                            Devvy,
                            Can you open a service request on this and let me know the SR number? If you don't have a support account with Oracle, can you email me a simple test case with your TransactionScope problem? I'd like to find out if this is a bug that we need to fix in the beta. My email address is alex.keh [at] oracle.com.
                            • 11. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
                              703590
                              Thank you Greg, for saving what's left of my sanity. May blessings rain upon you!

                              I was getting access violations using TransactionScopes with Oracle XE, when a transaction scope got disposed. Changing the format of the connection string has fixed it.

                              \*head desk\*
                              • 12. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
                                747726
                                Hi Greg,

                                I am having the same error and your reply is helpful in resolving the problem.

                                However, is there any solution to overcome this problem while still keeping the fully qualified TNS Alias with TransactionScope? Reason being, we would prefer to put this in the client code configuration rather than setting up the TNS on each and every client when we deploy.

                                By the way, I am using Oracle 11g. Does the version has any effect?

                                Thanks very much for any help.
                                • 13. Re: 10g Express + ODP.NET (version 2.111.6.20) > support TransactionScope?
                                  Alex.Keh .Product.Manager-Oracle
                                  Try upgrading to ODP.NET 11.1.0.7.20 or 11.2. When doing some testing within Oracle, we saw the problem in ODP.NET 11.1.0.6.20, but didn't see the problem occur in ODP.NET 11.1.0.7.20.

                                  If you still see the problem, can you either file an SR or post a reproducible test case on the forum so that Oracle can investigate?