[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Rollback In Oracle Forms

Posted on 2002-05-10
8
Medium Priority
?
6,356 Views
Last Modified: 2007-12-19
When i am executing a Rollback statement is Oracle Forms , the entire form is getting cleared. I want to avoid this and rollback only the DML statements.
0
Comment
Question by:praveenw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 7001404
You can only use the Clear_form built_in in forms to do a Rollback.
To avoid the messages appearing, you can set the System.Message_Level variable accordingly.

Using rollback and savepoint commands to control transaction in Oracle Forms
should be avoided from the following reasons.

- Savepoints are a crucial part of an Oracle transaction
- And they are especially important for a Forms transaction
- The reason is Forms uses savepoints for it's internal processing
- And the Forms transaction must never be desynchronized with the database
 transaction
To guarantee the consistency of the Forms processing don't try to set a
savepoint of your own. It won't work anyway or it will break your application.
.
Here are some examples:
.
1) If you do this in PL/SQL in Forms:
    SAVE_POINT my_savepoint;
     ...
     ROLLBACK to my_savepoint;
  Forms will not rollback to my_savepoint.
  It will rollback to it's own savepoint.
  This could be the savepoint defined when the starting the app,
  or before starting the commit processing. When calling a new form
  with CALL_FORM a savepoint is set too. This savepoint is not set
  with OPEN_FORM. So a ROLLBACK TO <savepoint> in this case will
  be a rollback of the entire application.
.
2) If you use a ROLLBACK TO <savepoint> in a stored program
   unit, Forms doesn't see this. So you could rollback a part
   of the database transaction without rolling back the
   correlated part of the Forms transaction. Forms transaction
   processing has very closely tied to the database transaction
   processing. So be always very careful in this area.
.
3) The example for ISSUE_SAVEPOINT is in the Forms 6.0 Reference
   Manuel, page 275. It shows an ON-SAVEPOINT trigger used for
   running against a third party database without savepoints or
   with a savepoint processing different from the Oracle
   processing. If Forms runs with an Oracle database it uses
   the default processing, if not is uses a user exit.
.
Here some rules we should follow:
.
- Don't use the SAVEPOINT or ROLLBACK TO <savepoint> commands
  in Forms. You won't achieve what you want.
.
- Don't use SAVEPOINT, ROLLBACK TO <savepoint>, ROLLBACK or
  COMMIT in stored program units. You'll desynchronize Forms
  and the database. There is an exception to this rule
  which will be explained later.
.
- Don't use ISSUE_SAVEPOINT and ISSUE_ROLLBACK other than
  the documented way.
.
Here is the exception to the second rule which might provide
a solution for your problem. If you really need
a ROLLBACK or a COMMIT in a stored program unit, because
you cannot have it in local PL/SQL, do this:
.
- call a new Form with OPEN_FORM(...,SESSION)
- from this form call the stored program unit
.
So you have a different transaction. Everything you do there
doesn't hurt the existing Forms transaction. Just ensure
that you don't use any Forms default transaction processing
in this second transaction.
.
But in most cases you could do the same thing with a second
transaction and Forms default processing. What was designed
to be a rollback to savepoint first, could here be designed
as a full rollback of the second transaction. This is another
rule you always should keep in mind when programming with Forms:
.
Always try to use Forms default processing:
- otherwise you have to do much more coding
- in most cases the default processing is faster than programmed
  solutions.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7001447
Schwertner makes some good points, I agree with him.  If you use the "Clear_form" built-in you will get a rollback.  Any other attempt to do a rollback in a form that does not involve clearing the data off the screen is unsupported and is likely to be dangerous and/or difficult to manage.

Can you give us some more details on the kind of  situation where you think it would be helpful (and safe) to be able to do a rollback without clearing the form?
0
 

Author Comment

by:praveenw
ID: 7001820
In my application I am inserting & updating  data in multiple tables using INSERT and UPDATE Statements( Here i am not using any Data blocks), so if any exception occurs while inserting or updating then how can I rollback the previous statements if I do not use ROLLBACK.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 7002072
Using Oracle Forms to do inserts and updates without using blocks based on database tables or views seems like a complex way to get the job done.  Is there a reason you do not want to use the default functionality of Oracle Forms to handle the inserts and updates?
0
 

Author Comment

by:praveenw
ID: 7002843
Thanks for the comments made by schwertner and  markgeer.

I would like to explain a scenario in my application which may help in understanding the problem in a better way.

The main functionality of my Application is moving the data (from several tables) of one user to another user.

Let say there are two users "UserA" and "UserB" and now i want to move some(Selected data on the Screen) of UserA  to UserB. So, In such type of situations a data block may not be helpful and one has to uses INSERT statements to move the data to the other user.

So, is there any alternative way?




0
 

Author Comment

by:praveenw
ID: 7002844
Thanks for the comments made by schwertner and  markgeer.

I would like to explain a scenario in my application which may help in understanding the problem in a better way.

The main functionality of my Application is moving the data (from several tables) of one user to another user.

Let say there are two users UserA and UserB and now i want to move some(Selected data on the Screen) of UserA  to UserB. So, In such type of situations a data block may not be helpful and one has to uses INSERT statements to move the data to the other user.

So is there any alternative way?




0
 
LVL 48

Accepted Solution

by:
schwertner earned 400 total points
ID: 7002856
If you select records in schema A and insert these record (with possible modifications)to the schema B you have to have the rights to do this in the both of schemas. The same right gives you the possibility to base a block on table(s) in schema A and another block on table(s) in schema B. It is easy using INSERT_RECORD to add records to Forms blocks. If your blocks are based not on a single table but on many tables you can either use POST-QUERY and PRE-INSERT triggers to manipulate the records  OR you can use updatable views with INSTEAD OF triggers (I have answered on questions how to do this). So you will be able to enjoy the embeded Forms functionality including ROLLBACK for free and very comfortable programmatically.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 7002877
Markgeer made a important, fundamental and correct comment.
Before to dave into technical details you have to read again the Marks' remark and to analyze whether you are using the right tool for your task. Forms primerely is designed for front end GUI interface - entering and modifying of tables data, but it emphsize on the displaying of data on the screen and manipulating it, using GUI objects like buttons, scrollbars, checkboxes, radiobuttons etc. to
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question