From: hillmw@ram.lmtas.lmco.com   
      
   > I just tried to change some in test and of course it wouldn't let me change   
   any because of the   
   > constraints. I was afraid of that.   
      
   >   
   > Mike, I have actually gone through and migrated all related rows in an   
   > application where the parent table key was a sequence value.   
   > Logically it was not that hard to write the pl/sql code I used to   
   > perform the processing but it was a fairly expensive process from the   
   > point of view of the work that has to be done by Oracle, indexes   
   > updated, redo generated, etc....   
   >   
   > Here is an alternate that we are about to do today.  Due to a sequence   
   > that is about to recycle (due to size limitation of external system we   
   > feed) and where we must first remove existing data to allow the reuse   
   > of the sequence value, but where the customer does not want any data   
   > removed until after year-end since this will distort their   
   > year-to-date information, is to substitute our own home-grown   
   > sequence.   
   >   
      
   So all you are doing is re-using the holes that exist. Do you have an example   
   of that code. This may be a   
   better idea.   
      
   >   
   > What I did was write a function as an anonymous transaction that reads   
   > one row from an IOT that was populated with the missing (skipped)   
   > sequence numbers using select for update, delete, commit, return the   
   > selected value.   
   >   
   > It seems to test just fine.  As long as you are on Oracle version 8i   
   > and the function is not called as part of a distributed transaction   
   > this may be a simplier route to take.  With version 9 the distributed   
   > transaction restriction should be removed.  We just substituted our   
   > function name for the sequence, recompiled the four programs that call   
   > the sequence, and tested.   
   >   
   > HTH -- Mark D Powell --   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)