OraFAQ Forum: SQL & PL/SQL » Retrying a transaction after SERIALIZABLE error

Home » SQL & PL/SQL » SQL & PL/SQL » Retrying a transaction after SERIALIZABLE error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Retrying a transaction after SERIALIZABLE error [message #2971]

Tue, 27 August 2002 03:08

Mark Grimshaw
Messages: 73
Registered: June 2002

Member

Hi,
I am having difficult implementing a retry of a procedure call in a SERIALIZABLE level PL/SQL client script that calls a Stored procedure.

This is my client test script:-

SET TRANSACTION LEVEL SERIALIZABLE;

...
...

LOOP
SAVEPOINT TRY_EDIT;

MyPackage.EditRecord(theRecord);
EXIT;

END LOOP;

EXCEPTION WHEN INVALID_UPDATE_RECORD_CHANGED THEN
ROLLBACK TO TRY_EDIT;

COMMIT;

END;

I want to retry the procedure call if the exception is caught but I can't seem to get it to work correctly.

Note:INVALID_UPDATE_RECORD_CHANGED is an exception that is raised by the procedure in MyPackage after the Serializable error/exception has been caught.

Can anyone spot what I am doing wrong. I have tried various permutations of the ordering of the statements involved.

Thanks

Mark Grimshaw

Report message to a moderator

Re: Retrying a transaction after SERIALIZABLE error [message #2975 is a reply to message #2971]

Tue, 27 August 2002 11:28

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Mark, have you tried a structure like:

begin
  loop
    savepoint try_edit;

    begin
      mypackage.editrecord(therecord);
      commit;
      exit;
    exception 
      when invalid_update_record_changed then
        rollback to try_edit;
    end;
  end loop;
end;

Report message to a moderator

Previous Topic:	querying a collection
Next Topic:	pl/sql stored procedure errors

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 22 00:59:34 CDT 2025