Re: Using Set Transation statement??

If you have a complex transaction, you can set a transaction marker, and do some database updates, and rollback to the marker if there is an error. Prior database updates are not committed or rolled back so processing can continue. Otherwise, the error can rollback all updates done to that point. If there is an error in the subsequent processing, you have an opportunity to roll the whole thing back.

Stuff coming in on a web page can fit this model - want to capture the name, address,etc, but not post an order if the CC check fails. Set transaction marker (A) after insertion of name, address, set another one (B) after insertion of order, set another one (C) after reserving inventory to ship, do CC check & commit if successful or rollback to (C) or (B) depending on the CC check results. (ie if no funds rollback inventory reservation (C), if a stolen card, rollback order (B) as well..... then insert stolen card message, insert text as a record of the order attempt, & commit. Locks in name & address & message but no order... obviously too simple an example.) Useful to remove complex if-then processing, especially if you have lots of packaged procedures & any can generate an error/rollback.

HTH EM

Jim Kennedy wrote:

> You have a large transaction. That way you can assign it to a large
> rollback segment. If you do not then you can generate a snapshot too old
> error.
>
> "JAP" <jasonperez_at_mindspring.com> wrote in message
> news:94dlmj$ebh$2_at_slb7.atl.mindspring.net...
> > I am reviewing PL/SQL for my OCP test. I understand the syntax of <Set
> > Transaction Use Rollback Segment random_label; > but I don't understand
 why
> > it exists. Why would you us a Set Transaction? Does anyone have a tangible
> > real-world type example? The book uses a banking systems example (Sybex;
> > Oracle8i DBA SQL and PL/SQL study guide; page 183, Chip Dawes, Biju
 Thomas).
> >
> > Thanks for the help.
> > JAP
> >
> >
> >
> >
> >
Received on Wed Jan 24 2001 - 15:38:37 CST