Re: 9i SAVEPOINT question

In article <1132943173.78669_at_yasure>, DA Morgan says...
> 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.

Good.

> 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?

Every "master" record that is migrated has its original and new ID recorded in a transaction table. Upon resumption of the process, it simply ignores records that have already been entered into the table. Pretty simple.

> In short, to use your word: inelegant.
> It also opens the door to substantial human error.

Ignorance led to the above approach, however it does work and, on this occasion, reworking for the error-free and elegant approach is not viable.

>
> 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.
>

Next time.

As I said, I wanted to know if the approach was flawed in any respect other than "it is not the best way to do it" - and you have confirmed that.

Many thanks.

cheers

-- 

jeremy