I know the data is there somewhere. I'm running an update loop (5k
rows/commit) to avoid the rollback segment too small issue. It works
fine, but I have to do a count of the remaining rows to see if I need
to loop again. It would be much faster if I could just see if the
previous commit did 5k rows or not. How do I trap the "# of rows
affected" from the commit ?

Thanks !

On Fri, 31 Oct 2003 05:19:52 -0800, Richard Elliott wrote:

> I know the data is there somewhere. I'm running an update loop (5k
> rows/commit) to avoid the rollback segment too small issue. It works
> fine, but I have to do a count of the remaining rows to see if I need
> to loop again. It would be much faster if I could just see if the
> previous commit did 5k rows or not. How do I trap the "# of rows
> affected" from the commit ?
>
> Thanks !

This might (!) help :

http://www.jlcomp.demon.co.uk/faq/rowcount.html


Cheers,
Norm.

--
Delete the obvious bit from my email address to reply by email.

why not just make your rollback segments the right size?

--
Niall Litchfield
Oracle DBA
Audit Commission Uk
"Richard Elliott" <richard.a.elliott@williams.com> wrote in message
news:8c132b3c.0310310519.2963db99@posting.google.c om...
> I know the data is there somewhere. I'm running an update loop (5k
> rows/commit) to avoid the rollback segment too small issue. It works
> fine, but I have to do a count of the remaining rows to see if I need
> to loop again. It would be much faster if I could just see if the
> previous commit did 5k rows or not. How do I trap the "# of rows
> affected" from the commit ?
>
> Thanks !

Glad to see my Hard Work hasn't been wasted.

"Niall Litchfield" <n-litchfield@audit-commission.gov.uk> wrote in message
news:3fa26cff$0$254$ed9e5944@reading.news.pipex.ne t...
> why not just make your rollback segments the right size?
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission Uk
> "Richard Elliott" <richard.a.elliott@williams.com> wrote in message
> news:8c132b3c.0310310519.2963db99@posting.google.c om...
> > I know the data is there somewhere. I'm running an update loop (5k
> > rows/commit) to avoid the rollback segment too small issue. It works
> > fine, but I have to do a count of the remaining rows to see if I need
> > to loop again. It would be much faster if I could just see if the
> > previous commit did 5k rows or not. How do I trap the "# of rows
> > affected" from the commit ?
> >
> > Thanks !
>
>

"Alkos" <azerty@nospam.org> wrote in message news:<bntof1$m9i3@news.rd.francetelecom.fr>...
> What about using a implicit cursor within a PL/SQL proc ?
> Cursors have a fancy property called %ROWCOUNT
>
> On the other hand, what about enlarging your RBS (if you can) ?
>
> "Richard Elliott" <richard.a.elliott@williams.com> a écrit dans le message
> news: 8c132b3c.0310310519.2963db99@posting.google.com...
> > I know the data is there somewhere. I'm running an update loop (5k
> > rows/commit) to avoid the rollback segment too small issue. It works
> > fine, but I have to do a count of the remaining rows to see if I need
> > to loop again. It would be much faster if I could just see if the
> > previous commit did 5k rows or not. How do I trap the "# of rows
> > affected" from the commit ?
> >
> > Thanks !

Let me add some more detail to explain why I ask the question and
haven't considered the obvious. What I am doing is setting a flag in
each row based on other values in the row. The table has 48 Million
rows, and I expect to update about 5 million of them. No rollback
segment is going to allow that to happen as one transaction. I am
using a pl/sql procedure, ,but not a cursor. I don't think, (but I
will try it), I can cursor up that many rows (5 million). The column
is a new one and will be maintained by the ins/upd trigger going
forward. Just need to get it initilized to start with. Thanks for all
the feedback !

Thanks for all the

Actually you need to trap the rowcount before the commit, otherwise the
rowcount is back to either zero or 1. uisng SQL%ROWCOUNT works with both
implicit and explicit cursors.


"Richard Elliott" <richard.a.elliott@williams.com> wrote in message
news:8c132b3c.0310311220.10c3903c@posting.google.c om...
> "Alkos" <azerty@nospam.org> wrote in message
news:<bntof1$m9i3@news.rd.francetelecom.fr>...
> > What about using a implicit cursor within a PL/SQL proc ?
> > Cursors have a fancy property called %ROWCOUNT
> >
> > On the other hand, what about enlarging your RBS (if you can) ?
> >
> > "Richard Elliott" <richard.a.elliott@williams.com> a écrit dans le
message
> > news: 8c132b3c.0310310519.2963db99@posting.google.com...
> > > I know the data is there somewhere. I'm running an update loop (5k
> > > rows/commit) to avoid the rollback segment too small issue. It works
> > > fine, but I have to do a count of the remaining rows to see if I need
> > > to loop again. It would be much faster if I could just see if the
> > > previous commit did 5k rows or not. How do I trap the "# of rows
> > > affected" from the commit ?
> > >
> > > Thanks !
>
> Let me add some more detail to explain why I ask the question and
> haven't considered the obvious. What I am doing is setting a flag in
> each row based on other values in the row. The table has 48 Million
> rows, and I expect to update about 5 million of them. No rollback
> segment is going to allow that to happen as one transaction. I am
> using a pl/sql procedure, ,but not a cursor. I don't think, (but I
> will try it), I can cursor up that many rows (5 million). The column
> is a new one and will be maintained by the ins/upd trigger going
> forward. Just need to get it initilized to start with. Thanks for all
> the feedback !
>
> Thanks for all the

Thanks !
What I am doing is actulay traping the rowcount from the update, not
the commit. SQL%ROWCOUNT is doing exactly what I needed, thanks to
everyone for the very usefull feedback.

"Keith Jamieson" <keith_jamieson@hotmail.com> wrote in message news:<aq7pb.3651$bD.15268@news.indigo.ie>...
> Actually you need to trap the rowcount before the commit, otherwise the
> rowcount is back to either zero or 1. uisng SQL%ROWCOUNT works with both
> implicit and explicit cursors.
>
>
> "Richard Elliott" <richard.a.elliott@williams.com> wrote in message
> news:8c132b3c.0310311220.10c3903c@posting.google.c om...
> > "Alkos" <azerty@nospam.org> wrote in message
> news:<bntof1$m9i3@news.rd.francetelecom.fr>...
> > > What about using a implicit cursor within a PL/SQL proc ?
> > > Cursors have a fancy property called %ROWCOUNT
> > >
> > > On the other hand, what about enlarging your RBS (if you can) ?
> > >
> > > "Richard Elliott" <richard.a.elliott@williams.com> a écrit dans le
> message
> > > news: 8c132b3c.0310310519.2963db99@posting.google.com...
> > > > I know the data is there somewhere. I'm running an update loop (5k
> > > > rows/commit) to avoid the rollback segment too small issue. It works
> > > > fine, but I have to do a count of the remaining rows to see if I need
> > > > to loop again. It would be much faster if I could just see if the
> > > > previous commit did 5k rows or not. How do I trap the "# of rows
> > > > affected" from the commit ?
> > > >
> > > > Thanks !
> >
> > Let me add some more detail to explain why I ask the question and
> > haven't considered the obvious. What I am doing is setting a flag in
> > each row based on other values in the row. The table has 48 Million
> > rows, and I expect to update about 5 million of them. No rollback
> > segment is going to allow that to happen as one transaction. I am
> > using a pl/sql procedure, ,but not a cursor. I don't think, (but I
> > will try it), I can cursor up that many rows (5 million). The column
> > is a new one and will be maintained by the ins/upd trigger going
> > forward. Just need to get it initilized to start with. Thanks for all
> > the feedback !
> >
> > Thanks for all the