Adding Rows to Global Temp Table accross DBLINK not working with Oracle Encryption

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
hyiothesia
Posts: 22
Joined: Thu 23 May 2013 20:37

Adding Rows to Global Temp Table accross DBLINK not working with Oracle Encryption

Post by hyiothesia » Tue 27 Sep 2016 20:13

We recently turned on oracle encryption. We switched over to using non-Direct mode and thus the oracle client rather than the devart built-in client. We have a DBLink in place to another database. In the other database we have a global temp table. In our database we have a view across the DBlink to the global temp table. In our code (below) we are trying to add rows to the table via the view.

I have a similar issue.

Code: Select all

using ( AfmContext afmContext = GetDataContext() )
{
    using ( TransactionScope transactionScope = new TransactionScope() )
    {
         // CODE REMOVED HERE
    try
    {
        foreach (var spIdOrPremId in spIdsOrPremIds.Where( id => id != null ) )
        {
            afmContext.CmGisSpidPremidListVws.Add(new CmGisSpidPremidListVw(spIdOrPremId));
        }
       afmContext.DbContext.SaveChanges();

        // CODE REMOVED HERE that calls views in other database that 
        // depend on the temp table being populated
    }
    finally
    {
        // This causes a commit.   Without the transaction logic the Save Changes call will
        // send commit.  A commit clears the temp table, which we don't want above, but we 
        // need this here to clear the temp table for the next call.
        transactionScope.Complete();
    }
    return customerInfos.ToList();
}
It gets a "ORA-24778: cannot open connections" DbUpdateException on the SaveChanges line.

All this worked fine until the oracle encryption change. Any idea what I need to modify to get this working?

Thanks!

hyiothesia
Posts: 22
Joined: Thu 23 May 2013 20:37

Re: Adding Rows to Global Temp Table accross DBLINK not working with Oracle Encryption

Post by hyiothesia » Tue 27 Sep 2016 22:18

I created a test solution to get rid of the other code and also updated to EF 6 (using EF 4 in the other product)

Code: Select all

            using (SdeDbEntities context = new SdeDbEntities(SdeConnectionString))
            {
                using (TransactionScope transactionScope = new TransactionScope())
                {
                    var spId = new CmGisSpidPremidListVw();
                    spId.SpidPremid = "0123450123";
                    context.CmGisSpidPremidListVws.AddObject(spId);
                    context.SaveChanges();
                    Console.WriteLine("Successfully wrote spid to global temp table");
                    foreach (string en in context.CmGisCustInfoBySpIdVws.Select(x => x.EntityName))
                    {
                        Console.WriteLine(en);
                    }
                    Console.WriteLine("Successfully got customer data");
                }
            }
I am now getting a Devart.Data.Oracle.OracleException: ORA-24777: use of non-migratable database link not allowed.

This occurs when pointed at a database with encryption turned on or at a database with encryption turned off. The culprit seems to be the change in the connection string to Direct=False;

Any ideas?

Thanks.

hyiothesia
Posts: 22
Joined: Thu 23 May 2013 20:37

Re: Adding Rows to Global Temp Table accross DBLINK not working with Oracle Encryption

Post by hyiothesia » Tue 27 Sep 2016 22:35

Connections strings I have tried:

Works with No Encryption, can't work at all with encryption:
"metadata=res://*/SdeDb.csdl|res://*/SdeDb.ssdl|res://*/SdeDb.msl;provider=Devart.Data.Oracle;provider connection string=""User Id={username};Password={Password};Server={Sid}host;Sid={Sid};Direct=True;Persist Security Info=True;Min Pool Size=1;Trim Fixed Char=false"""


Both of these strings get this error with the second code sample and EF 6.
ORA-24777: use of non-migratable database link not allowed
and get this error with the first code sample and EF 4
ORA-24778: cannot open connections

"metadata=res://*/SdeDb.csdl|res://*/SdeDb.ssdl|res://*/SdeDb.msl;provider=Devart.Data.Oracle;provider connection string=""User Id={username};Password={Password};Data Source={Sid};Persist Security Info=True;Min Pool Size=1;Trim Fixed Char=false"""

"metadata=res://*/SdeDb.csdl|res://*/SdeDb.ssdl|res://*/SdeDb.msl;provider=Devart.Data.Oracle;provider connection string=""User Id={username};Password={Password};Server={Sid}.world;Home=oraclient12home1;Direct=False;Persist Security Info=True"""

hyiothesia
Posts: 22
Joined: Thu 23 May 2013 20:37

Re: Adding Rows to Global Temp Table accross DBLINK not working with Oracle Encryption

Post by hyiothesia » Wed 28 Sep 2016 00:03

I found that adding the following to the connection string resolves the issue:

";Transaction Scope Local=True"

However, this is not a good solution for me as I have a LOT of code sharing connections in the application. I am looking to see if there is a way to override just this transaction.

hyiothesia
Posts: 22
Joined: Thu 23 May 2013 20:37

Re: Adding Rows to Global Temp Table accross DBLINK not working with Oracle Encryption

Post by hyiothesia » Wed 28 Sep 2016 00:43

This works.

I had to change from using TransactionScope to manually beginning a Transaction from the oracle Connection object and commiting the transaction in the finally block.

In EF4 (the first code sample) the change looks like this:

Code: Select all

using ( AfmContext afmContext = GetDataContext() )
{
    DbTransaction transaction = afmContext.DbContext.ObjectContext.Connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
         // CODE REMOVED HERE
    try
    {
        foreach (var spIdOrPremId in spIdsOrPremIds.Where( id => id != null ) )
        {
            afmContext.CmGisSpidPremidListVws.Add(new CmGisSpidPremidListVw(spIdOrPremId));
        }
       afmContext.DbContext.SaveChanges();

        // CODE REMOVED HERE that calls views in other database that 
        // depend on the temp table being populated
    }
    finally
    {
        // This causes a commit.   Without the transaction logic the Save Changes call will
        // send commit.  A commit clears the temp table, which we don't want above, but we 
        // need this here to clear the temp table for the next call.
        transaction.Commit();
    }
    return customerInfos.ToList();
}

In EF6 (the second code sample) the change looks like this:

Code: Select all

            using (SdeDbEntities context = new SdeDbEntities(SdeConnectionString))
            {
                context.Connection.Open();
                DbTransaction transaction = context.Connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                try
                {
                    var spId = new CmGisSpidPremidListVw();
                    spId.SpidPremid = "6230132040";
                    context.CmGisSpidPremidListVws.AddObject(spId);
                    context.SaveChanges();
                    Console.WriteLine("Successfully wrote spid to global temp table");
                    foreach (string en in context.CmGisCustInfoBySpIdVws.Select(x => x.EntityName))
                    {
                        Console.WriteLine("Successfully Got Customer: {0}", en);
                    }
                }
                finally
                {
                    transaction.Commit();
                }
            }

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Adding Rows to Global Temp Table accross DBLINK not working with Oracle Encryption

Post by Shalex » Wed 28 Sep 2016 11:51

Please confirm that the issue is solved and no investigation is required.

hyiothesia
Posts: 22
Joined: Thu 23 May 2013 20:37

Re: Adding Rows to Global Temp Table accross DBLINK not working with Oracle Encryption

Post by hyiothesia » Wed 28 Sep 2016 15:47

Yes, issue is solved. Thanks.

hyiothesia
Posts: 22
Joined: Thu 23 May 2013 20:37

Re: Adding Rows to Global Temp Table accross DBLINK not working with Oracle Encryption

Post by hyiothesia » Thu 29 Sep 2016 21:43

On second thought I do have another issue. We found another spot where we are getting this error when we are only querying across the dblink, not updating, while in a transaction. To make it more complicated the transaction is across MANY calls to create connections.

I have looked at the following links
https://www.devart.com/dotconnect/oracl ... tions.html
https://www.devart.com/dotconnect/oracl ... tring.html

and I know that setting "Transaction Scope Local=true;" in the connection string fixes the issue.

However it sounds like doing so will group all my connections into one connection internally, where before (with direct mode) I was getting one transaction per connection internally, but still having multiple connections.

I really do not have an option to change to a local transaction in code in this case as the transaction in the new code contains MANY calls that open a new connection, do a query, and dispose the connection.

The documentation does seem to imply that the Transaction Scope Local = True connection single instance logic only applies while in a transaction. In the vast majority of our code we do NOT use transactions so in those cases multiple connections would be used even with the Transaction Scope Local = True on the connection string. Correct?

It would still be better if I could make it behave EXACTLY as it did before but with oracle encryption, however, this (connection string change) might work. Is there some way to force the OCI mode to internally use one transaction per connection but still have multiple connections as it did with Direct Mode?

Thanks.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Adding Rows to Global Temp Table accross DBLINK not working with Oracle Encryption

Post by Shalex » Mon 03 Oct 2016 14:32

hyiothesia wrote:The documentation does seem to imply that the Transaction Scope Local = True connection single instance logic only applies while in a transaction. In the vast majority of our code we do NOT use transactions so in those cases multiple connections would be used even with the Transaction Scope Local = True on the connection string. Correct?
The case is not clear. The "Transaction Scope Local = True" can be applied only within TransactionScope. If there is no TransactionScope, the local transaction will be committed before returning connection to pool (if pooling is used).
hyiothesia wrote:It would still be better if I could make it behave EXACTLY as it did before but with oracle encryption, however, this (connection string change) might work. Is there some way to force the OCI mode to internally use one transaction per connection but still have multiple connections as it did with Direct Mode?
Are these connections within TransactionScope or not? If TransactionScope is not used, you can start local transaction for each connection: https://www.devart.com/dotconnect/oracl ... html#local.

hyiothesia
Posts: 22
Joined: Thu 23 May 2013 20:37

Re: Adding Rows to Global Temp Table accross DBLINK not working with Oracle Encryption

Post by hyiothesia » Mon 03 Oct 2016 16:04

Anything with a transaction is using TransactionScope, everything else is not using a transaction at all.

Given what you have said
"Transaction Scope Local = True" can be applied only within TransactionScope
this setting should work for us.

Thank you!

Post Reply