Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Global search/replace
Sounds pretty dangerous to me! I think I would want to know where it is occurring before, I would go about updating up to 50 tables in my database, but to each his own... ;)
Create a stored proc with the following logic:
1. put the following SQL in a cursor:
SELECT
tablename
, columnname
FROM
user_tab_columns;
2. loop through this cursor, using dynamic sql to change the table and columnname in your update statement using either the DBMS_SQL built-in or EXECUTE_IMMEDIATE (v8.1.5 I think).
3. use DBMS_OUTPUT.PUT_LINE to output the number of updates for each column/table, and DON'T put a COMMIT in your code. COMMIT from the command line, if you are satified with the results.
HTH,
Michael J. Ort
In article <8d3smc$kq5$1_at_nnrp1.deja.com>,
crazyCoder <crazycoder_at_my-deja.com> wrote:
> I need to change the occurance of a string throughout my database (all
> tables). The problem is that I have over 50 tables and I don't know
> where all the string appears. Is there a script/program/application
> which will traverse all the data in all the tables and do a global
> replace?
>
> Thanks,
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Apr 13 2000 - 00:00:00 CDT