Robert Fazio <dbabob_at_yahoo.nospam.com> wrote in message news:<Xns9100E39873FDDdbabobyahoocom_at_24.12.106.199>...
> "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in
> news:3b7b93cc$0$233$ed9e5944_at_reading.news.pipex.net:
> > CURSOR C_REMOTE IS
> > <some select statement>
> >
> >
> > FOR remote_rec in C_REMOTE LOOP
> > IF i mod 1000 THEN
> > <insert statement>
> > COMMIT;
> > ELSE
> > <insert statement>
> > END IF
> > i:= i + 1
> > END LOOP
> >>
> >> I'm going to pull data from a remote table, insert it into a local
> >> table, and I want to perform commits
>
> Be aware that if you commit, and fetch from an open cursor you stand the
> chance to hit ORA-01555. If you really want to commit along the way. Open
> and close the select cursor.
If the original post was reading and inserting to the same table then
the 'snapshot too old' ORA-01555 error would be a real possibility;
however, since the original post was about reading a remote table into
a local table this is not a high probability, but if the error were to
occur I think the first step would be to try to increase the row count
between commits rather than close and reopen the cursor since they
have no efficient way of identifing which rows have already been
processed.
Received on Sun Aug 19 2001 - 10:07:27 CDT
