Re: "savepoint" feature

On Thu, 12 Jul 2007 13:28:57 -0700, "mike7411_at_gmail.com" <mike7411_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