Re: [Urgent] Rollback in Store Procedure

A copy of this was sent to "Sam" <sam_at_email.tech-trans.com> (if that email address didn't require changing) On Wed, 29 Dec 1999 17:19:28 +0800, you wrote:

>Hi,
>
>Can I call ROLLBACK in store procedure? I write a store procedure in
>Oracle 8i with PL/SQL. Whenever it call rollback, it throws an exception:
>
>java.sql.SQLException: [Oracle][ODBC][Ora]ORA-02074: cannot ROLLBACK in a
>distributed transaction.
>
>The rollback statement is put in the exception session of the outermost
>block.
>

are you sure it is in the outermost block in the originating server??

You can only commit/rollback in the database that originated the transaction. This means if you call a stored procedure via a database link (it is in another database) IT cannot commit -- only the caller from the originating database can commit (because only they are aware of all of the participants in this distributed transaction).

>Any suggestion?
>
>Sam
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 29 1999 - 06:12:27 CST