Oracle stored procedure with db link doesn't work from C#?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I am calling an Oracle stored procedure which has no input or output parameters, and only does an insert to a table on a remote Oracle server, like this
PROCEDURE Joe_test I
BEGI
insert into leslie@acc_db2 values ('insert')
commit
END

When I call it from C# using System.Data.OracleClient, I get the exception
ORA-02041: client database did not begin a transactio
ORA-06512: at "OPS$JOE.JOE_TEST", line
ORA-06512: at line

If I change it to insert into a local table instead of a remote one, it works. If I run it as is from SQL/Plus, it works. It only fails when I run it from .NET with System.Data.OracleCommand.ExecuteNonQuery()

I have tried using "enlist=false" in the database connect string. This actually seems to solve the problem if I use ODP instead of System.Data.OracleClient, but has no effect with the latter. I don't have a good understanding of what it does other than changing the default handling of distributed transactions. System.Data.OracleClient is supposed to handle distributed transactions
I would appreciate any help or suggestions!
 
.... and the Oracle 8.1.7 client on Windows 2000, accessing an Oracle 8.1.7 database also on W2K.
 
In the oracle manuals they write...
ORA-02041 client database did not begin a transaction
Cause: An update occurred at a coordinated database without the coordinator
beginning a distributed transaction. This may happen if a stored procedure
commits and then performs updates, and the stored procedure is invoked
remotely. It could also happen if an external transaction monitor violates
the XA protocol.
Action: If the cause is the former, check that any commit is not followed by
an update.

....or try to call the procedure inside a transaction

Joseph Crum again said:
... and the Oracle 8.1.7 client on Windows 2000, accessing an Oracle 8.1.7
Click to expand...
database also on W2K.
 
Hello Joseph,

You are running into Oracle Client 8.1 client bug 914652. This problem was
exposed when we added Distributed transaction support to the Oracle Client
v1.1. There is a QFE available from our side that allows you to completely
disable distributed transactions by using a connection string keyword
(Enlist=false is not enough), please contact pss directly for the fix.

A better option would be to switch to a newer version of the Oracle Client,
this issue is fixed in v9.x
Hope this helps
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.


Joseph Crum again said:
... and the Oracle 8.1.7 client on Windows 2000, accessing an Oracle 8.1.7
Click to expand...
database also on W2K.
 
Back
Top