Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Global search/replace
CrazyCoder,
I just completed a similar process in which I had to renumber the values in certain columns of many tables based on a separate conversion table of old-number, new-number.
You can follow Michael Ort's process, along with his cautions (well advised!). I followed a similar approach, however, I inserted many more manual steps to be sure the results would be correct. I also did NOT commit until the very end, when I was happy with the result. (This also included a number of tests in which I rolled back.
Here is a "modified Ort" showing some additional steps, in principle.
Good Luck,
Robert Proffitt
Beckman Coulter
RTProffitt_at_beckman.com
(want the commits here because just saving to temp tbl). 2a. Execute the spool file (do all the inserts). Spool to a log file 2b. Examine log file...
3. Examine temp table contents, see if all is ok. 4. Make change to text in temp table.
update temptbl set thetext = ....substr/instrb/ function to replace text... 5. Use the temp table to construct SQL to update. Select 'update '||table_name||' set '||column_Name|| ' = '''||TheText||' where ....etc...;' From temptbl
Much more manual than using a stored procedure,
but same idea... Sorry, I am just overly
paranoid and "detail-oriented" on big changes
like this... especially if it is a one-shot,
I would much rather go slow and do manual steps
and be confident of my results than be "trick" and
write a "cool" stored procedure....
(you know, that feeling of when you write a slick
algorithm...:-)
Now, if the thing has to be done routinely,
then it's probably worth all the effort to
test and troubleshoot the stored procedure.
RTP
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Apr 13 2000 - 00:00:00 CDT