Devart Forums

When we use the dotConnect for Oracle with views connected via db link, we get the error:"ORA-01453: SET TRANSACTION must be first statement of transaction when we fire 2 statements, first a SELECT and then a INSERT"

In pseude code >>>>>>

SELECT * FROM {VIEW_TABLE_VIA_DBLINK}
----------------------------------
Timestamp: 10:26:14.965

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
----------------------------------
Timestamp: 10:26:15.417

insert into {TABLE_LOCAL}(COLUMN)
values (:p)
p0 = 'lorum'
----------------------------------
Timestamp: 10:26:15.485


If we change the DBLINK into a local TABLE the statements work correctly, with a DBLINK we get an ORA-01453 error.

The same problem is also described in:
http://www.mydatabasesupport.com/forums/oracle-server/
52354-when-using-dblink-ora-01453-set-transaction-must-first-statement-transaction.html

What is the solution to this problem?

We think this error is not caused by our provider. Try using ODP.NET or Microsoft .NET Provider for Oracle. Probably, you will obtain the same result. Check your database link; maybe a reason of the error is in it. Our provider is responsible for connecting to the database only, but the database link you are using is implemented in the Oracle database itself.

I've checked the 5.0.22 version of dotConnect for Oracle. I created view bestview in ORA920 ("select * from dept"), made database link ORA920.SCOTT in my ORA1020, and then executed the following statement in the OracleCommand object: "select * from [email protected]" when was connected to ORA1020. It works OK.

Try placing your OracleCommand execution in the transaction block. Maybe it will help.

I think it has nothing todo with the Oracle provider we are using. The error is generated by the Oracle server itself (I can even reproduce it in TOAD)

We are using the entity framework. Your software is generating the statement "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"

FYI: The problem occurs when we use an Insert statement after a Select statement.

Shalex wrote:We think this error is not caused by our provider. Try using ODP.NET or Microsoft .NET Provider for Oracle. Probably, you will obtain the same result. Check your database link; maybe a reason of the error is in it. Our provider is responsible for connecting to the database only, but the database link you are using is implemented in the Oracle database itself.

I've checked the 5.0.22 version of dotConnect for Oracle. I created view bestview in ORA920 ("select * from dept"), made database link ORA920.SCOTT in my ORA1020, and then executed the following statement in the OracleCommand object: "select * from [email protected]" when was connected to ORA1020. It works OK.

Try placing your OracleCommand execution in the transaction block. Maybe it will help.

I've solved the problem by starting the transaction before the first select statement which accesses the dblink.