Home » Developer & Programmer » Forms » Rollback Issue
Rollback Issue [message #490530] Tue, 25 January 2011 08:06 Go to next message
piyarustagi
Messages: 38
Registered: January 2011
Member
I'm trying to put a savepoint and rollback in a when-button-pressed trigger, but it's not working. Can anybody explain me why?

[MOD-EDIT: code removed upon user request but here's the short version]

Begin
  FORMS_DDL('SAVEPOINT B');
  ...
  UPDATE_TABLE1; -- performing DML
  ...
  UPDATE_TABLE2; -- performing DML
  ...
  IF a_block.a_field = a_value THEN
     FORMS_DDL('ROLLBACK TO B'); -- Does not work. Why?
  END IF;
  ...
END;

[Updated on: Tue, 25 January 2011 09:37] by Moderator

Report message to a moderator

Re: Rollback Issue [message #490531 is a reply to message #490530] Tue, 25 January 2011 08:12 Go to previous messageGo to next message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
1) Can you please read and follow the orafaq forum guide - especially the part on how to format your post. Your code is quite hard to follow as it stands.
2) What DB changes do you expect to be rolled back? I don't see any in the code.
Re: Rollback Issue [message #490534 is a reply to message #490530] Tue, 25 January 2011 08:15 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
You do realize that the code posted is not valid, right? Apart from that, what do you mean when you say "it's not working"? Does the forms runtime process crash? Do you get an erorr message? Does the form seem to hang? Are the changes saved to the database even though there's a rollback in the code?

Edit: cookiemonster, we can only assume that the "UPDATE_TABLE1", "UPDATE_TABLE2" and the like are procedures/functions that perform some dml.

MHE

[Updated on: Tue, 25 January 2011 09:34]

Report message to a moderator

Re: Rollback Issue [message #490535 is a reply to message #490534] Tue, 25 January 2011 08:50 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I just remembered that Forms does not handle savepoints very well. Oracle support agrees, see Metalink document "How to Control a Transaction in Oracle Forms ? [ID 154098.1]".

You have to rethink your program because it will not work like you designed it.

MHE
Re: Rollback Issue [message #490536 is a reply to message #490534] Tue, 25 January 2011 08:56 Go to previous messageGo to next message
piyarustagi
Messages: 38
Registered: January 2011
Member
My apology for the format.
Yes, you are right. DDL is getting performed in these functions UPDATE_LOT,UPDATE_COMPASS,POST_UPDATE_LOT.
What I want is that if that IF condition is satisfied, it should rollback to savepoint B and all the database changes should be rolled back.
It's not working means database changes are not getting reverted. They remain the same.

I'm putting the code again... this is the original one...
I have put the SAVEPOINT AND ROLLBACK in the code... rest is the original code.
If you have any other solution to do that, I will appreciate that too.

Code ............
DECLARE
  ...
  <snip>
  ...
BEGIN
   IF :B1.C1 IS NULL
   THEN
      WARNING_ALERT ('Please enter a new layout number.');
      RAISE Form_Trigger_Failure;
   ELSE
      /* check for any pending jobs on this
   layout */
      IF A_FUNCTION (:C1.A_FIELD) != 1
      THEN
         FORMS_DDL ('SAVEPOINT B');
         ...
         <snip>
         ...
      IF :B1.C2 = 'A_VALUE' THEN
         FORMS_DDL ('ROLLBACK to B');
         FORMS_DDL ('COMMIT');
      END IF;

      CLEAR_FORM (No_Validate);
      GO_BLOCK ('CLT');
      ...
      <snip>
      ...
      SET_BLOCK_PROPERTY ('C1', ONETIME_WHERE, 'C35=''2');
      EXECUTE_QUERY;
      ...
      <snip>
      ...
   END IF;
END;


[MOD-EDIT: changed code upon user request]

[Updated on: Tue, 25 January 2011 09:37] by Moderator

Report message to a moderator

Re: Rollback Issue [message #490538 is a reply to message #490536] Tue, 25 January 2011 09:03 Go to previous messageGo to next message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
DDL? Are you sure you don't mean DML?
You can't roll ddl back under any circumstances.
Assuming you do mean dml then as Maaher points out, oracle themselves say this doesn't work in forms.
So you need to restructure the code so that you only do the dml if you aren't going to want to do a rollback.
Re: Rollback Issue [message #490539 is a reply to message #490538] Tue, 25 January 2011 09:11 Go to previous messageGo to next message
piyarustagi
Messages: 38
Registered: January 2011
Member
Sorry,
yes DML....
is there any other solution to revert the changes.
Re: Rollback Issue [message #490541 is a reply to message #490539] Tue, 25 January 2011 09:13 Go to previous messageGo to next message
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not according to that metalink note.
Re: Rollback Issue [message #490543 is a reply to message #490541] Tue, 25 January 2011 09:15 Go to previous messageGo to next message
piyarustagi
Messages: 38
Registered: January 2011
Member
Thanks a lot to both of u..... I highly appreciate your help.
Re: Rollback Issue [message #490549 is a reply to message #490543] Tue, 25 January 2011 09:33 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I've changed the code to a more "generic" piece so we still can get an idea what this thread was all about. There's nothing proprietary anymore. Is that ok?

MHE
Re: Rollback Issue [message #490550 is a reply to message #490549] Tue, 25 January 2011 09:35 Go to previous messageGo to next message
piyarustagi
Messages: 38
Registered: January 2011
Member
COOOL...
are u the owner of this website? Really good website, I must say.
Re: Rollback Issue [message #490581 is a reply to message #490550] Tue, 25 January 2011 15:37 Go to previous messageGo to next message
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you consider creating a stored procedure (which would look almost the same as the one you put into the WHEN-BUTTON-PRESSED trigger)? Its parameters would be data block items you use in the code. You'd then call that procedure from the WHEN-BUTTON-PRESSED trigger. But - as the procedure is now in the database - you might use savepoints.
Re: Rollback Issue [message #490582 is a reply to message #490581] Tue, 25 January 2011 15:40 Go to previous messageGo to next message
piyarustagi
Messages: 38
Registered: January 2011
Member
seems like a good idea, I'll try that tomm.
Re: Rollback Issue [message #490694 is a reply to message #490581] Wed, 26 January 2011 14:24 Go to previous message
piyarustagi
Messages: 38
Registered: January 2011
Member
Thanks everybody, the issue is resolved now.
I created a new procedure inside form and reverted the changes in that and called it in the main.
Previous Topic: How to create cookie from Oracle forms
Next Topic: Setting Tab Sequence
Goto Forum:
  


Current Time: Tue Sep 17 16:18:30 CDT 2024