Solved

Savepoints, Rollbacks in forms

Posted on 1999-01-14
2
1,641 Views
Last Modified: 2008-02-20
I issue an insert into statement and before comitting I issue a clear_block(no_validate) to cancel the transaction, but the insert statement is not rolled back.  When I issue a rollback the command will delete my headings(title, datetime, userid etc.) off of the form.  I have tried using a savepoint that I set just after I set up my headings on the form and isssue the rollback to savepoint, but it still does not seem to work.  Can I rollback my insert into statement without clearing out my complete form?
0
Comment
Question by:murphydp
2 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 1083672
A "clear_block" will only undo uncommitted inserts, updates or deletes in the current block.  It will not rollback any other statements that the form may have done since the last commit.

I usually save the userid and current date in global variables (Forms4.5 or earlier) or in a client-side package (Forms5.0 or later) and reset them in the form in the when-clear-form trigger.  That technique will not prevent the problem you are having, but it may give you a work around.
0
 

Accepted Solution

by:
jsanand earned 50 total points
ID: 1083673
Dear Murphydp,

You are right !!! The userid and other fields are changed to null when you issue a rollback command in forms. This is because forms internally issues a clear form for this.

To solve you problem you should use the form built in's

ISSUE_SAVEPOINT(<savepoint_name>);
ISSUE_ROLLBACK(<savepoint_name>);

Before entering the block mark a save point and issue a rollback when requires using the form builtin

Another work around to you problem is !!
based on certain condition the ON_INSERT trigger should fail
eg
  ON_INSERT trigger
 
  Begin
    If EVERYTHING_OK then
       INSERT_RECORD;
    Else
       NULL;
  End;

This way the record will not be inserted into the database and you even do not need to issue a rollback command

Hope this will solve your problems
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

679 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