Re: "savepoint" feature

On Jul 12, 4:39 pm, sybra..._at_hccnet.nl wrote:
> On Thu, 12 Jul 2007 13:28:57 -0700, "mike7..._at_gmail.com"
>
>
>
>
>
> <mike7..._at_gmail.com> wrote:
> >On Jul 12, 1:05 pm, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> >> mike7..._at_gmail.com wrote:
> >> > I am working with the "savepoint" feature in Oracle, and I noticed
> >> > that a savepoint appears
> >> > to get deleted when a commit occurs. I was wondering if these is an
> >> > easy way to make
> >> > a savepoint that will persist across commits.
>
> >> > Any help is appreciated.
>
> >> > Thank you.
>
> >> A savepoint lets you roll back part of a transaction. Without a
> >> savepoint, the only option is to roll back the entire transaction. Once
> >> you commit the transaction, it can no longer be rolled back....in part
> >> nor in total. So it makes no sense to let you roll back to a savepoint
> >> in a committed transaction. This is not possible. If you think you need
> >> to rollback to that savepoint, then don't commit!
>
> >> HTH,
> >> Brian
>
> >> --
> >> ===================================================================
>
> >Unfortunately, I am calling into someone else's stored procedures,
> >which have commits in them.
>
> >I am not allowed to modify their procedures to remove the commits. Is
> >there any Oracle feature
> >that can be of help in this case? I may need to rollback after some
> >unknown number of commits have
> >occurred.
>
> As your Oracle doesn't seem to have a version, a proper answer can not
> be provided.
> Be aware though that your 'rollback' may involve using LogMiner (for
> which you would need to have the SCNs to 'rollback')
> or flashback (and you can flashback either individual tables or the
> complete database.
> Logminer exists in 8i and higher, flashback from 9i and higher.
> I have the feeling though your 'requirement' is best met by dumping
> the PL/SQL ore modifying it, as the work arounds will be extreemly
> complicated and likely very unstable.
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Hide quoted text -
>
> - Show quoted text -

Mike, I agree with Brain and Sybrand. A savepoint that spans commits makes no sense and you need to dump the pre-written routines or remove the commits.

Generally speaking commits should be done by the caller and not within a called process.

HTH -- Mark D Powell -- Received on Thu Jul 12 2007 - 17:09:48 CDT