Re: 9i SAVEPOINT question

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 24 Nov 2005 13:55:01 -0800
Message-ID: <1132943173.78669@yasure>

Jeremy wrote:
> In article <1132861969.236090_at_yasure>, DA Morgan says...
>

>>Savepoints are designed to solve a different problem. Could you get them
>>to work? Perhaps but at a huge cost in terms of manual labor. It makes
>>far more sense to use the tool designed for the job.
>>

>
>
>
> Hi Daniel, I am pursuing this issue with you because I am keen to know
> whether the solution I came up with (being ignorant of DBMS_REUSABLE)
> appears to function as hoped having tested the routine and raised an
> exception at different points we find the results are rolled back to the
> latest savepoint.
>
> My question to you is what is the huge cost of manual labour? 5 minutes
> to code, 15 to test thoroughly and the job is done (unless there is
> something I don't realise). This is for a once-off migration process.
>
> Your knowledge and experience of Oracle database is highly regarded so
> would be very grateful to get a specific "no it doesn't work" or "yes it
> works but is inelegant and inflexible and incurs obscene system
> overheads" or womething along those lines.
>
> cheers

Rolled back to the most recent savepoint? Absolutely. But now you have to manually restart at the correct location. How are you going to do that?
Query what was loaded, manually edit the file, restart? In short, to use your word: inelegant.
It also opens the door to substantial human error.

You are certainly welcome to do it the hard way ... and you can. I would suggest, however, you use the built-in capability and add it to your resume.

Run the demo at the bottom of my web page and I think you will agree.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Thu Nov 24 2005 - 15:55:01 CST