Rollback Issue [message #490530] |
Tue, 25 January 2011 08:06 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](//www.gravatar.com/avatar/e1d73457f8673a06ca22c7d5406b8447?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13963 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](/forum/images/custom_avatars/43055.jpg) |
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 #490536 is a reply to message #490534] |
Tue, 25 January 2011 08:56 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](//www.gravatar.com/avatar/e1d73457f8673a06ca22c7d5406b8447?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
cookiemonster
Messages: 13963 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.
|
|
|
|
|
|
|
|
|
|
|