Re: PL/SQL Question

I apologise, I must have missed the brackets when reading your original post.

As for the other issue, eg what happens when the power goes out, the answer is that the entire copy fails;

Every procedure when it is complete, records its status in a table. So if the power goes out, we should know what the last successfully executed procedure was.

We then have a choice. We can start by manually running the procedure which failed, and the rest of the procedures, and manually update a table with the time that these procedures were called(recorded in the same table mentioned above)
or we can resubmit the entire job. The decision would depend on how many of the procedures had been physically copied. (There are only 17 in total).

These procedures are supposed to be run nightly, so I don't envisage a huge volume of data being transferred.

Thanks for your Input, anyway.

Thomas J. Kyte wrote in message <8cd6lh$91k$1_at_nnrp1.deja.com>...
>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 Wed Apr 05 2000 - 00:00:00 CEST