DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: ORA-02074: cannot SET SAVEPOINT in a distributed transaction

  1. #1
    Join Date
    May 2006
    Posts
    1

    ORA-02074: cannot SET SAVEPOINT in a distributed transaction

    Hi .
    I've developed a very simple stored procedure that writes in two database tables and depending on internal result, can rollback or commit the transaction.
    The procedure works fine as the desired user from sql script Toad but when it is called from a web page the error returned is Insert User ORA-02074: cannot SET SAVEPOINT in a distributed transaction Code-2074.
    I've read that to get around this problem i have to declare the stored procedure as pragma autonomous_transaction but it doesn't work well.
    The client code uses the Enterprise Library for .NET Framework 2.0. The Oracle database is 8.1.7.3.

    The S.P. code is like this:

    create or replace procedure insert_into_t
    as
    pragma autonomous_transaction;

    myCondition boolean ;
    begin

    SAVEPOINT A;

    insert into t values ( 1 );

    if myCondition then
    commit;
    else
    rollback to A;
    end if;
    end;



    Thanks - Davide.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    Maybe you need to re-write your very simple stored procedure.

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Feb 2005
    Posts
    158
    Autonomous transaction is a bad idea. It won't see any uncommitted changes from your main transaction, and the commit would only apply to the insert in your autonomous transaction.

    If you can't use savepoints, you'll need to actually do the test before the insert, so you can NOT INSERT, rather than INSERT/ROLLBACK.

    create or replace procedure insert_into_t
    as
    myCondition boolean ;
    begin
    if myCondition then
    insert into t values ( 1 );
    commit;
    end if;
    end;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width