Re: Global search/replace

From: <rtproffitt_at_my-deja.com>
Date: 2000/04/13
Message-ID: <8d5701$3ud$1_at_nnrp1.deja.com>#1/1

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

1. Find the tables and columns.
   > SELECT
   > table_name
   > , column_name
   > FROM
   > user_tab_columns
   Where column_Name in (a,b,c,d,e...) (or similarly...all the string columns. Where column_type = 'VARCHAR2'... etc)
2. Use this to create dynamic SQL and populate a temp table, or print the results to spool file... (or spool to a file in csv format for Excel, for easier handling).. Select 'INSERT INTO TEMPTBL ..ETC... ('|| ' SELECT '|| ' '''||TABLE_NAME||''' , '''||COLUMN_NAME||''','|| COLUMN_NAME FROM '||TABLE_NAME||' WHERE '|| COLUMN_NAME ' LIKE ''%searchstring%'');'||chr(10)|| 'COMMIT;' From user_tab_columns Where ....etc...

   (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

   (You may have to modify the SQL and temptable to     include enough info to uniquely identify the row     being updated...--where key=a and key2=b etc...) 6. Examine the update statements....
6a. Execute the spool file... spool to a log file. 6b. Examine the log file
7. Happy? then commit. Bad, then rollback, no harm done.

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 CEST