I have a problem with an Oracle database link...

[MikeDamone]

Hello all,

I am trying to execute a query that uses a database link. I am using the OracleConnection object. When i try to execute the query I get the following error:

ORA-02041: client database did not begin a transaction

I have researched this and found out that using an OleDBConnection you can disable MTS Support to get around this. However, I don't want to rework all my calls to the DB to use OleDB. Does anyone know how to get around this using the OracleConnection object? Thanks

 

[obislavu]

A solution is to execute a stored procedure using the OleDBConnection and access from this stored procedure another database using the dblink that you know and you are allowed to access.
Eventually you can pass the dblink/user/password to the stored procedure in your code, then in the procedure check the availability of the dblink and query data you need.
-obislavu-

 

[MikeDamone]

Thanks obislavu. I ended up finding a soultion while still using the OracleConnection object rather than the OleDBConnection object. The following line of code place in my stored procedure allows it to function correctly.

SET TRANSACTION READ ONLY;

I'm guessing here, but I think that the way .Net is talking to Oracle is causing Oracle to think there is distrubited transaction update coming because of the database link. I think the above line lets Oracle know that its a simple read only select being executed.