Re: ORA-02064 - problem with commit in a remote function/procedure

A copy of this was sent to Henrik.Bachmann_at_bim-consulting.de (Henrik Bachmann) (if that email address didn't require changing) On Tue, 24 Nov 1998 08:27:46 GMT, you wrote:

>Hi there anybody!
>
>I need help with the following database link:
>
>CREATE DATABASE LINK dbLink
>CONNECT TO user IDENTIFIED BY pwd
>USING 'db';
>

[snip]

>
>There are no transactions in the local function:
>

Oh yes there is -- it is part of the transaction taking place on the other site. Before each procedure call, there is an implicit SAVEPOINT and after it, there is an implicity ROLLBACKK to savepoint (only executed if the invoked procedure throws an unhandled exception).

[snip]

>
>The transaction is only in the remote function. If I want to commit
>this transaction I run into an
>oracle error: ORA-02064. The problem is the commit on the remote site,
>but why and what can I do? I need this commit on the remote site. The
>local site needs only the return value of the remote function
>(0 ... success, 1 ... error).
>

You must commit in the database that initiated the transaction (the local database). The remote database cannot 'commit' since the local database IS IN FACT part of the transaction -- you might not think it is but it is.

You might consider parameterizing test_remote2 and adding a 'commit' flag so that a remote call can tell it "do not commit, i will commit for you".

You simply will not be able to commit in the remote procedure -- no way, no how. It wasn't designed that way. All transaction control MUST come from the original site (how does the remote site understand that you were not in the middle of a 'real' transaction yourself? there is nothing preventing you from having an insert/update/delete statement right before and after the remote procedure call -- you are part of that transaction)

[snip]

>
>I hope anybody can help me.
>

only by putting the transaction logic into the involing routine will you succeed...

>Best regards
>
>Henrik Bachmann
>
>B.I.M.-Consulting Magdeburg mailto:Henrik.Bachmann_at_bim-consulting.de
>Lorenzweg 37-45
>D-39124 Magdeburg
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.