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

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

Postby 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?
ejkruis
 
Posts: 2
Joined: Mon 23 Feb 2009 10:18

Postby 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 bestview@ORA920.SCOTT" when was connected to ORA1020. It works OK.

Try placing your OracleCommand execution in the transaction block. Maybe it will help.
Shalex
Devart Team
 
Posts: 7206
Joined: Thu 14 Aug 2008 12:44

Postby 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 bestview@ORA920.SCOTT" 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

Postby 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.
noldenho
 
Posts: 1
Joined: Mon 16 Mar 2009 07:18


Return to dotConnect for Oracle