Re: PL/SQL Question

In article <8ccofi$gvp$1_at_kermit.esat.net>, "Keith Jamieson" <jamiesonk_at_phoenix.ie> wrote:
> Since commiting in a cursor for loop is bad practice, what do you
recommend
> as an alternative?
>
> I have procedures on an OLTP application which have to copy
> data from the OLTP's schema to a report scshema, and this is done
> nightly.
>

I said:

(its a bad practice
 and can lead to other errors - in particular ORA-1555 if you are  updating the table you are selecting from).

you are not updating the table you are selecting from so it might be OK.

Yours still might be a bad practice tho depending on how you answer:

what happens after you've copied 100,000 records and the power goes out leaving the remaining 50,000 records uncopied. What procedure do you have in place for a restart?

> In some instances, It is possible that a large number of rows is
needed to
> be copied, therefore, being pragmatic, I commit after every 1000
> inserts/updates.
>
> Interestingly enough, this did not prevent me from running out of
rollback
> segment space, when I was doing a distributed copy,
> so the calling procedure which performs the distributed copy now
specifies
> the name of a Large rollback segment which has specifically been
created for
> this purpose.
>
> I don't particularly like any solution where I have to hardcode
something,
> but I don't see any way around this at present.
>

--
Thomas Kyte tkyte_at_us.oracle.com
Oracle Service Industries http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 04 2000 - 11:53:14 CDT