BUG: DBLink: ORA-01453: SET TRANSACTION be first statement

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
ejkruis
Posts: 2
Joined: Mon 23 Feb 2009 10:18

BUG: DBLink: ORA-01453: SET TRANSACTION be first statement

Post by ejkruis » Mon 23 Feb 2009 10:33

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?

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

Post by Shalex » Mon 23 Feb 2009 15:26

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.

ejkruis
Posts: 2
Joined: Mon 23 Feb 2009 10:18

Post by ejkruis » Mon 23 Feb 2009 16:47

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.

noldenho
Posts: 1
Joined: Mon 16 Mar 2009 07:18

Post by noldenho » Mon 16 Mar 2009 07:21

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

Post Reply