Hi,

Whenever I call below method, I am receiving error "SET TRANSACTION must be the first statement...." at the line when it is StartTrasaction.

adapter is nothing by declared as DataAccessAdapterBase

public bool Save(TEntity entity, bool refetchAfterSave = false)
        {
            bool isSavedSuccessfully;

            using (var adapter = new TAdapter())
            {
                adapter.Commit();
                try
                {
                    adapter.ConnectionString = _connectionString;
                    adapter.StartTransaction(IsolationLevel.ReadCommitted, "SaveEntity" + DateTime.Now.ToString("ddMMyyyyhhmmss"));
                    isSavedSuccessfully = entity.Fields.State == EntityState.Deleted
                                                ? adapter.DeleteEntity(entity)
                                                : adapter.SaveEntity(entity, refetchAfterSave);
                    adapter.Commit();
                }
                catch
                {
                    adapter.Rollback();
                    throw;
                }
            }

            return isSavedSuccessfully;
        }

Error Trace: -

System.Data.OracleClient.OracleException was unhandled by user code HResult=-2146232008 Message=ORA-01453: SET TRANSACTION must be first statement of transaction

Source=System.Data.OracleClient ErrorCode=-2146232008 Code=1453 StackTrace: at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) at System.Data.OracleClient.OracleCommand.ExecuteNonQuery() at System.Data.OracleClient.OracleTransaction..ctor(OracleConnection connection, IsolationLevel isolationLevel) at System.Data.OracleClient.OracleInternalConnection.BeginOracleTransaction(IsolationLevel il) at System.Data.OracleClient.OracleInternalConnection.BeginTransaction(IsolationLevel il) at System.Data.OracleClient.OracleConnection.BeginDbTransaction(IsolationLevel isolationLevel) at System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel) at SD.Tools.OrmProfiler.Interceptor.ProfilerDbConnection.BeginDbTransaction(IsolationLevel isolationLevel) in c:\Myprojects\VS.NET Projects\OrmProfilerv1.5_hg\SD.Tools.OrmProfiler.Interceptor\ProfilerDbConnection.cs:line 224 at System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateNewPhysicalTransaction() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.StartTransaction(IsolationLevel isolationLevelToUse, String name) at Volt.VTX.Framework.GenPro.EntityWriteManager3.Save(TEntity entity, Boolean refetchAfterSave) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Framework\GenPro\EntityWriteManager.cs:line 62 at Volt.VTX.Domain.Candidate.Managers.CandidateDupCheckSessionManager.Save(CandidateDupCheckSessionEntity entity) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Domain.Candidate\Managers\CandidateDupCheckSessionManager.cs:line 67 at Volt.VTX.Domain.Candidate.Managers.CandidateDupCheckSessionManager.CheckForDuplicateCandidates(CandidateEntity sourceCandidate) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Domain.Candidate\Managers\CandidateDupCheckSessionManager.cs:line 98 at Volt.VTX.Domain.Candidate.Managers.CandidateManager.AddCandidateWithDupCheck(CandidateEntity candidate, ValidationResults& results) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Domain.Candidate\Managers\CandidateManager.cs:line 87 at Volt.VTX.Service.Candidate.CandidateService.AddCandidateWithDupCheck(CandidateEntity candidate, ValidationResults& results) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Service\Candidate\CandidateService.cs:line 73 at Volt.VTX.Service.Test.Candidate.CandidateServiceTest.<>c__DisplayClass81.<AddCandidateWithDupCheck_ReturnsPotentialMatches_WhenEmailAddressMatch_Scenario1>b__7d(ICandidateService s) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Service.Test\Candidate\CandidateServiceTest..cs:line 1763 at Volt.VTX.Service.DataPortal.CandidateServiceProxy.InvokeSafe[T](Func2 operation, ProxyMode proxyMode) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Service.DataPortal\DomainServiceProxy.cs:line 346 at Volt.VTX.Service.Test.Candidate.CandidateServiceTest.AddCandidateWithDupCheck_ReturnsPotentialMatches_WhenEmailAddressMatch_Scenario1() in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Service.Test\Candidate\CandidateServiceTest..cs:line 1763 InnerException:

if you switch off orm profiler, does it work then?

What's the llblgen pro version and what's teh ODP.NET version?

Even after removing ORM Profiler, it gives the same error.

LLBL Genpro version 3.5 ODP.Net version 2.112.1.2 Oracle 11g

System.Data.OracleClient.OracleException was unhandled by user code HResult=-2146232008 Message=ORA-01453: SET TRANSACTION must be first statement of transaction

Source=System.Data.OracleClient ErrorCode=-2146232008 Code=1453 StackTrace: at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals) at System.Data.OracleClient.OracleCommand.ExecuteNonQueryInternal(Boolean needRowid, OciRowidDescriptor& rowidDescriptor) at System.Data.OracleClient.OracleCommand.ExecuteNonQuery() at System.Data.OracleClient.OracleTransaction..ctor(OracleConnection connection, IsolationLevel isolationLevel) at System.Data.OracleClient.OracleInternalConnection.BeginOracleTransaction(IsolationLevel il) at System.Data.OracleClient.OracleInternalConnection.BeginTransaction(IsolationLevel il) at System.Data.OracleClient.OracleConnection.BeginDbTransaction(IsolationLevel isolationLevel) at System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CreateNewPhysicalTransaction() at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.StartTransaction(IsolationLevel isolationLevelToUse, String name) at Volt.VTX.Framework.GenPro.EntityWriteManager3.Save(TEntity entity, Boolean refetchAfterSave) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Framework\GenPro\EntityWriteManager.cs:line 62 at Volt.VTX.Domain.Candidate.Managers.CandidateDupCheckSessionManager.Save(CandidateDupCheckSessionEntity entity) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Domain.Candidate\Managers\CandidateDupCheckSessionManager.cs:line 67 at Volt.VTX.Domain.Candidate.Managers.CandidateDupCheckSessionManager.CheckForDuplicateCandidates(CandidateEntity sourceCandidate) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Domain.Candidate\Managers\CandidateDupCheckSessionManager.cs:line 98 at Volt.VTX.Domain.Candidate.Managers.CandidateManager.AddCandidateWithDupCheck(CandidateEntity candidate, ValidationResults& results) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Domain.Candidate\Managers\CandidateManager.cs:line 87 at Volt.VTX.Service.Candidate.CandidateService.AddCandidateWithDupCheck(CandidateEntity candidate, ValidationResults& results) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Service\Candidate\CandidateService.cs:line 73 at Volt.VTX.Service.Test.Candidate.CandidateServiceTest.<>c__DisplayClass81.<AddCandidateWithDupCheck_ReturnsPotentialMatches_WhenEmailAddressMatch_Scenario1>b__7d(ICandidateService s) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Service.Test\Candidate\CandidateServiceTest..cs:line 1763 at Volt.VTX.Service.DataPortal.CandidateServiceProxy.InvokeSafe[T](Func2 operation, ProxyMode proxyMode) in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Service.DataPortal\DomainServiceProxy.cs:line 346 at Volt.VTX.Service.Test.Candidate.CandidateServiceTest.AddCandidateWithDupCheck_ReturnsPotentialMatches_WhenEmailAddressMatch_Scenario1() in C:\Development\30 VTX v1.0\20 VTX\Volt.VTX.Service.Test\Candidate\CandidateServiceTest..cs:line 1763 InnerException:

Hi Krunal,

• What is TAdapter? (please post your class)
• Is that a custom DataAccessAdapter subtype?
• If so, Do you start a transaction in there somehow (at the ctor, for instance)?

btw, System.Data.OracleClient is the MS Oracle client, not ODP.NET.

Did you reference the right Oracle DQE?

Hi David,

TAdapter is an instantiated object of DataAccessAdapterBase class.

public class EntityWriteManager<TEntity, TEntityCollection, TAdapter> : IEntityWriteManager<TEntity, TEntityCollection>
        where TEntity : class, IEntity2, new()
        where TEntityCollection : class, IEntityCollection2, new()
        where TAdapter : DataAccessAdapterBase, new()
    {}

daelmo wrote:

Hi Krunal,

• What is TAdapter? (please post your class)
• Is that a custom DataAccessAdapter subtype?
• If so, Do you start a transaction in there somehow (at the ctor, for instance)?

Not sure what is the difference though. As I put transactions back in and it all works perfectly ok.

Right now, I have removed the transaction from EntityManager, as it is starting transaction even for GetEntity and FindEntity. EntityManager is nothing but an extension to Genpro Framework.

Otis wrote:

btw, System.Data.OracleClient is the MS Oracle client, not ODP.NET.

Did you reference the right Oracle DQE?

        using (var adapter = new TAdapter())
        {
            adapter.Commit();

Why are you calling Commit on the adapter, after initializing it, and before starting any transaction?

That is one of try to resolve the issue.

As stated in below post-- http://www.techonthenet.com/oracle/errors/ora01453.php

Just for the sake of testing, What if you do this:

 using (var adapter = new DataAccessAdapter())
            {
                try
                {
                    adapter.ConnectionString = _connectionString;
                    adapter.StartTransaction(IsolationLevel.ReadCommitted, "SaveEntity" + DateTime.Now.ToString("ddMMyyyyhhmmss"));
                    isSavedSuccessfully = entity.Fields.State == EntityState.Deleted
                                                ? adapter.DeleteEntity(entity)
                                                : adapter.SaveEntity(entity, refetchAfterSave);
                    adapter.Commit();
                }
                catch
                {
                    adapter.Rollback();
                    throw;
                }
            }

... or using your original code (TAdapter, witthout the first adapter.Commit) and with this change in your TAdapter:

public class EntityWriteManager<TEntity, TEntityCollection, TAdapter> : IEntityWriteManager<TEntity, TEntityCollection>
        where TEntity : class, IEntity2, new()
        where TEntityCollection : class, IEntityCollection2, new()
        where TAdapter : IDataAccessAdapter, new()
    {}

Why are you using that EntityWriteManager? and Why are you instantiating DataAccessAdapterBase? Maybe i don't understand your custom class.

Additionally, if you need the COmmit at the ctor level of the adapter, you have dangling transactions somewhere so your code forgets to commit a transaction and at the same time re-uses the same adapter. Especially this last part is important as it's not recommended to re-use adapter instances unless multiple calls have to share a single transaction. Creating adapters takes almost no time as there's almost no overhead in the ctor.