On Tue, September 30, 2008 06:44, Jens wrote: > > Thanks for the feedback. Certainly helped the thought process :-) > A question with regard to your second suggestion: > > How would you go about getting the rows blockwise into the other > table? > > The problem now is that my reader should process all rows, but > that the way i'm doing it causes rows to be skipped sometimes. > Your proposition seems to suggest that there may be a another solution > for reading blockwise from the table without the risk of skipping > rows. > I was thinking of selecting on rownum instead, so that it would be > effectively > selecting the rows that were "physically" inserted last in the table. > But that has problems, because rownum may changes if the table is re- > ordered > at a later point. Also it's not easy storing a rownum value as it is > it's own > special data type (i've tried); > > > > > > > > none of the solutions I offered would skip rows because they are designed to ONLY return the rows that are unprocessed.
ROWNUM is NOT reliable because sequence of rows it is NOT GUARANTEED to be the same, even between two invocations of the same select. Do not be fooled by the fact that it is USUALLY the case because this can get you into all kinds of trouble. If you mean ROWID... that is a different issue .. think of it as a magic cookie. It might LOOK sequential but it is not. As for your other point, there is no need to write them blockwise although if you read them into a collection you could insert them in bulk. Personally I would add them individually since that minimizes the chance of a failure halfway through the process tossing away hard work by the reader process, but that is just me, I hate reruns. You can do this by ensuring that your select does NOT do a "for update" and that your "insert into a new table and delete the old record" procedure/function is set up as an autonomous transaction. It could be a bit of a performance hit although depending on volume that might not be a big deal. Rob --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---